Store and Query Spatial Data in Microsoft SQL Server 2008

Store Data

Spatial data is stored by using Geometry and Geography data types that are introduced in Microsoft SQL Server 2008. Geometry data type can be created as follows :

CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO

The data into the geometry data column is persisted by using the following INSERT command

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

Query Data

The data in the geometry data columns is queried by using the following Commands.

DECLARE and SELECT statements:

DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

MERGE Statement in Microsoft SQL Server 2008

Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a condition. The target table for certain operations is based on the results of join with a source table. 

For example

MERGE InventoryMaster AS invmstr
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON invmstr. InventoryID = src. InventoryID
WHEN MATCHED THEN
          UPDATE SET invmstr.Descr = src.Descr
WHEN NOT MATCHED THEN

INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);

Database Mirroring Enhanced in Microsoft SQL Server 2008

Data base mirroring in Microsoft SQL Server 2008 is enhanced by following ways :

Automatic Page Repair

The principal is enabled and mirror computers for recovering transparently from 823 and 824 errors on data pages, with a request for a fresh copy of the page that is corrupted from the mirroring partner.

Improved Performance

The outgoing log stream is compressed by Microsoft SQL Server 2008 for minimizing the network bandwidth that is required by database mirroring.

Enhanced Supportability

Additional performance counters for enabling more granular accounting of the time, which is spent across the different stages of DBMS log processing. Dynamic Management Views and extensions of the existing views are included, for exposing additional information that is concerned to mirroring sessions.

Encryption and External Key Management in Microsoft SQL Server 2008

A comprehensive solution for encryption and key management is provided by Microsoft SQL Server 2008. The growing need for greater information security within the data centers of enterprises is managed by security keys. 


This could be done by excellent support given by Microsoft SQL Server 2008, by supporting third-party keys management and hardware security module products.

Plan Freezing in Microsoft SQL Server 2008

Plan freezing is a new concept that allows the DBAs to persist plan guides. These guides could be reverted to when the queries either fail or drain the resources after the upgrade. The stability to queries is achieved by Plan Freezing. 


Several monitoring features for checking when the query is succeeded or failed are included with Plan Freezing.