Microsoft SQL Server 2008 Filestream Storage

The complexity of application development reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.

There are 2 new capabilities in Microsoft SQL Server 2008 for persisting BLOB data.

1. FILESTREAM
2. Remote BLOB storage

FILESTREAM

It is an attribute that can be set on a ‘varbinary’ column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.

Remote BLOB storage

It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data. SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary’.

Compression Backup in Microsoft SQL Server 2008

A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.

The following processes can be isolated for obtaining a good picture.

1. Physical disk counters
2. Device throughput bytes / second counter of SQL Server Backup Device object
3. Backup/Restore throughput / second counter of SQL Server Databases object.

Microsoft SQL Server 2008 Policy Based Administration Feature

Policy based database administration allows the DBA for managing the instances and objects of Microsoft SQL Server 2008 across the enterprise by a defined policies that are set. These policies are the rules and regulations which determine the possible ways of what to do and what not to do and the way the violations of policies are enforced and reported. The policies are well defined by using facets and conditions. The details of facets and conditions are below.

Facet

An object which contains the properties which well describes a component.

Condition

A logical expression that is defined on a facet. It is used for identification of acceptable property values of the facet.

Use Sparse Columns of SQL Server 2008

A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with Column sets and Filtered index.

Column sets

The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.

Filtered index

As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.

A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.

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();