SQL Server 2008 new features

http://www.databasejournal.com/features/mssql/article.php/3816486/10-New-SQL-Server-2008-Features.htm

Geospatial Data Types and Functions

Finally, some Geographic Information System (GIS) capabilities have been incorporated into SQL Server. With SQL Server 2008 two geospatial data types where introduced and a number of geographic functions. The two new data types are geometry and geography. The geometry data type is a planer data type that represents the Euclidean coordinate system, or more commonly called the flat earth model. The geography data type represents ellipsoidal data and represents the round earth model. With these new data types, you can identify a specific point on the earth, or other geographic artifacts like roads, lakes, cities, countries, etc.

With the support for the geometry and geography data types Microsoft provides a number of different types of Open Geospatial Consortium (OGC) methods. These methods allow you to define points, linestrings, polygon, distance, intersection, etc. These GIS enhancements should now allow you to easily incorporate mapping functionality into your applications.

Resource Governor

When you are running many different kinds of processes on your SQL Server machine, you need a way to control the resource intensive processes so they don’t consume all the resources of your machine. The Resource Governor is a way to control those processes that are resource hogs. With the Resource Governor, you can limit CPU and Memory resources for those sessions that can be identified as using excessive resources. The Resource Governor uses classifier functions to identify which workload a particular session should use. The classification of a session in turn associates them with Resource Pools. By limiting the amount of CPU and Memory associated with a Resource Pool, you can control the amount of resources classified sessions can consume. By using the Resource Governor available with SQL Server 2008, a DBA can better control resources usage to provide a balanced throughput of all sessions. The Resource Governor is only available with the Enterprise and Developer editions of SQL Server 2008.

Policy-Based Management

With Policy Management, you can consistently manage your SQL Server 2008 instances through a set of rules, known as policies. You can write policies that will control things like naming standards, server configuration, import/export requirements, etc. By using a standard set of policies across all your SQL Server instances, you can consistently manage your servers and minimize your administrative costs to enforce those policies.

A policy consists of a condition that is defined against a facet that is checked against the appropriate target SQL Server components associated with the facet. A facet is a single SQL Server component that contains properties. Here is a short list of some of the facets available: Credential, Data File, Database, Index, Login, Name, Schema, Server, Table, User and View. The condition is a logical expression that validates that the properties of the facet meet your policy requirements. By using the condition defined in a policy you can control what is appropriate and not appropriate for a given facet.

Policy Management is available in all editions of SQL Server 2008.

Table Compression

To help conserve on the amount of disk space consumed by a table and it indexes, Table Compression was introduced in SQL Server 2008. This feature is only available in the Enterprise and Developer editions of SQL Server 2008.

With Table Compression, there are two different types of compression that can be used: Row and Page. Row compression compresses the fixed fields to save additional space in a row. Page compression goes a step further than row compression. It first does row compression and then performs prefix compression and dictionary compression. Prefix Compression is the process of taking reoccurring column prefix values and storing them in a Compression Information (CI) structure stored in the header of a page, and then replaces the actual values with an index to the value stored in the CI structure. Dictionary Compression is the process of taking reoccurring values in a page and replacing them with the index of the value stored in the CI structure.

Since Table compression is done within the database engine, it is completely transparent to the application. From my limited testing of compressing, it appears that 40-60% space savings can be gained by using Table compression. Your compression savings might vary based on the data types you use and data stored in your database tables.

With table compression there is also the possibility to improve the elapsed time of some of your processes. The performance boost occurs because fewer I/Os need to be performed to read the data. Keep in mind that the additional overhead to compress and decompress also is a cost associated with Table compression. Therefore, to improve the elapsed time of your TSQL statement the cost savings associated with performing less I/Os needs to be greater than the additional CPU cost incurred by performing Table Compression.

Backup Compression

New in SQL Server 2008 Enterprise and Developer edition is database Backup Compression. With Backup Compression, the database backups are compressed as the backup file is being written. By using compressed backups, you can save valuable disks space if a disk device is the target for your backups. Compressing backup will help minimize the amount of space needed for your backup. This could help if you don’t have a lot of free disk space.

Because less I/O is needed to write compressed database backups to a file, you might see your backup processes run fasters. Keep in mind compressing backups is a CPU intensive process. So if you are running compressed backup statements while other CPU intensive processes are running both the backup and the processes will be affected if you max out the CPU capacity of your machine.

Data Collections/Management Data Warehouse

There is a new statistic gathering and reporting mechanism called Data Collection. By using Data Collection a DBA can easily collect, store and manage statistics about their SQL Server instances. These statistics are stored in a database known as the Management Data Warehouse. The Data Collection mechanism does not only collect data but it also purges old data. When you define data collections, you identify how long you want to keep the data. Using this retention period SQL Server has a built in processes to automatically purge your Data Collection data from the Management Data Warehouse.

Out of the box, Microsoft has provided three different system Data Collections. One collection collects statistics about queries that are run. Another one collects disk space information. The last Data Collection collects information about the System performance. You can also define your own custom data collections.

Also provided are some canned reports that report on the data collect by the system Data Collections. These reports allow you to view trend information from the data collected by these system Data Collections. The reports are feature rich with hyperlinks to allow you to drill down from a high-level report to more specific detailed report. Using these canned reports, you can get a good picture of what is going on within your server.

FILESTREAM

Much of the data we work with is unstructured data, like images, Word documents, etc. This type of data is typically called binary large objects (BLOBs). Now with SQL Server 2008 you are able use the database engine to manage and store BLOBs as an NTFS file using FILESTREAM. With FILESTREAM, you are able to store BLOBs that exceed 2 gigabytes of space. By default, SQL Server 2008 has disabled FILESTREAM storage. You need to turn it on to take advantage of this kind of storage. Storing your images, Word documents or other BLOBs as a FILESTREAM object allows these objects to be under the control of the database engine. This allows the database engine to backup and restore these objects as part of the database backup/restore process. Also having these objects controlled by the database allows the database engine to also control the security related to these objects. Therefore, users don’t have access to FILESTREAM objects unless they are granted access. FILESTREAM is supported by all editions of SQL Server 2008.

New Date and Time Data Types

Finally, Microsoft has realized applications do not always need a time value stored with a date, or a date stored with a time value. With SQL Server 2008 two new data types, DATE and TIME, became available. Now you can use these new data types to store just the component of a point in time that you need, either a DATE or a TIME. Also provided is a new date/time data type known as DATETIME2. DATETIME2 increases the amount of precision you can have on the time portion of your date/time value. With DATETIME2 data types, you can store up to 7 digits of precision with accuracy down to 100 nanoseconds. DATETIME2 is also a variable length field, meaning you can define the number of digits of precision you want to store for the time portion. Allowing you to identify the precision allows you to conserve disk space for your DATETIME2 columns. Lastly, a new DATETIMEOFFSET data type was introduced. This new date type allows you to store date and time values that are time zone aware. This data type also allows you to define the number of digits of time precision you require for your DATETIMEOFFSET values.

Transparent Data Encryption

Transparent Data Encryption is just want it sounds like. Transparent Data Encryption is the process of encrypting your databases at rest transparently from the application. The encryption is done as blocks are written to disks, and then are decompressed when they are read back from disk. So within the buffer pool the database engine works the same, since the data is unencrypted in the buffer pool. There is some increased overhead to perform Transparent Data Encryption. As a number of the other new features mentioned here, this feature is also only available in the Enterprise and Developer editions of SQL Server 2008. When you use Transparent Data Encryption your databases data is encrypted at rest. So if you detach a database it is encrypted and can’t be moved to another server unless the encryption keys are also moved to that other server. The database backups are also encrypted, so you can only restore them to other servers if you also backup and restore the encryption keys.

Change Data Capture

SQL Server 2008 is now able to track changes to your database overtime using a new feature called “Changed Data Capture”. This new change tracking feature is only available in the Enterprise Edition and Developer edition. Once a table in a database is enabled for change data capture all changes to that table are tracked by storing changes in a change table. The change table will contain one record for every INSERT that can be used to identify column values for the inserted records. Each time a DELETE is performed the change table will contain one record for each DELETE that will show the values in each column prior to the DELETE. When an UPDATE is preformed, against a change data capture enabled table, two records will be created in the change table, one with the updated column values and one with the original column values. By using change data capture, you can track changes that have occurred over time to your table. This kind of functionality is useful for applications, like a data warehouse load process that need to identify changes so they can correctly apply updates to track historical changes over time.

http://sqlhints.com/2011/09/11/new-features-in-sql-server-2008/

Following are the some of the new features of the Sql Server 2008 which are very helpful to the Sql Developers

1) Variable declaration allows initialization:

Prior to Sql Server 2008 to initialize a variable, we needed to first declare the variable and then we can initialize it by using SET/SELECT statement as shown below:

1 2	DECLARE @COUNT INT SET @COUNT =100

Now in Sql Server 2008 Variable declaration allows initialization similar to the one we do in C#. Now instead of writing two statements, we can write a single statement as below:

1	DECLARE @COUNT INT =100

[ALSO READ] New Features in Sql Server 2012

2) Insert multiple rows using single INSERT Statement

To understand this feature first create an Employee Table by using the below script:

1 2	CREATE TABLE DBO.Employee ( Id INT,  Name VARCHAR(50) )

Prior to Sql Server 2008, to insert multiple records we use to write statements like below:

1 2 3

INSERT INTO dbo.Employee VALUES(1,'Basavaraj') INSERT INTO dbo.Employee VALUES(2,'Shashank') INSERT INTO dbo.Employee VALUES(3,'Monty')

Now in Sql Server 2008 we can accomplish the same by writing script like below:

1 2 3 4

INSERT INTO dbo.Employee VALUES(1,'Basavaraj') ,        (2,'Shashank') ,        (3,'Monty')

3) Arithematic Assignment Operators
Now Sql Server 2008 also supports the Arithematic Assignment Operators like the below ones:

Operator Usage            Description +=       SET @x+=@y       Same as: SET @x = @x + @y -=       SET @x-=@y       Same as: SET @x = @x - @y *=       SET @x*=@y       Same as: SET @x = @x * @y /=       SET @x/=@y       Same as: SET @x = @x / @y %=       SET @x%=@y       Same as: SET @x = @x % @y

Example:

1 2 3

DEClARE @x INT =2 ,@y INT = 2 SET @x+=@y  SELECT @x as x,@y as y

Result: x           y ----------- ----------- 4           2

4) Table-Valued Parameters in Sql Server:
It provides option for the Client Applications to pass multiple rows of Data to Sql Server.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml /comma separated values and pass it to the stored procedure and in Stored Procedure convert this xml/comma separated values to a table variable/temporary table.

You can find detailed information on the Table-Valued Parameters and also on calling Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code @http://sqlhints.com/2011/09/09/table-valued-parameters-in-sql-server/

5) MERGE Statement

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

You can find detailed information on MERGE Statement @http://sqlhints.com/2011/09/24/merge-statement-in-sql-server-2008/

6) Sparse Column

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

You can find detailed information on Sparse Column @http://sqlhints.com/2011/11/12/sparse-columns-in-sql-server-2008/

7) Date and Time Data Types

Date, Time, DateTime2 etc are the new date and time data type introduced in SQL SERVER 2008. You can find detailed information on DateTime2 datatype in the article Difference between DateTime and DateTime2 DataType.

8) As SP_Depends results were not always reliable/accurate/correct. To resolve this in SQL SERVER 2008 following two DMV’s are introduced

sys.dm_sql_referencing_entities

sys.dm_sql_referenced_entities

9) Filtered Indexes

Filtered Index (i.e. Index with where clause) is one of the new feature introduced in Sql Server 2008. It is a non-clustered index, which can be used to index only subset of the records of a table. As it will have only the subset of the records, so the storage size will be less and hence they perform better from performance perspective compared to the classic non-clustered indexes.