T-SQL ENHANCEMENTS IN 2008

T-SQL Enhancements in 2008

Every new version of SQL Server comes with several T-SQL features and enhancements.

SQL Server 2008 is no exception. In the newest version of SQL Server, Microsoft has responded to developer requests in a big way. SQL Server 2008 supports brand new developer convenience features like single statement declaration and initialization, new data types to manipulate and query date-based and spatial data, new statements like MERGE that add much needed functionality to T-SQL and a whole lot more. Once you’ve made the move to SQL Server 2008, you’ll undoubtedly want to take advantage of the new features and functionality to solve common problems.

1. Separate Variable Declaration and Initialization

The first of these new features is single statement variable declaration and initialization. In the past, developers had to declare new variables in one or more DECLARE statements and assign values to the variables in one or more SET or SELECT statements as shown in below Listing.

DECLARE @x int, @y int, @z int

SET @x=1

SET @y=2

SET @z=3

With single statement declaration and initialization, you can combine these two steps into a single step as shown in below Listing

DECLARE @x int =1,@y int=2, @z int =3

2. Calculating and Assigning Values with C-Style Assignment

T-SQL has further improved with the addition of several C-Style assignment operators.

Operator	Description
+=	Addition with assignment, or string concatenation with assignment
-=	Subtraction with assignment
*=	Multiplication with assignment
/=	Division with assignment
%=	Modulo with assignment

Ex:

DECLARE @x int=4, @y int=25, @s1 varchar(20)=’Sql’

SET @x *= @y

SET @s1+= ‘SERVER’

SELECT @x, @s1

3. NEW INSERT STATEMENT

SYNTAX:

INSERT INTO TABLE_NAME

VALUES

(VALUE1, VALUE2,……),

(VALUE1, VALUE2,…..),

(VALUE1,VALU2,……..)

EX:

INSERT INTO EMP

VALUES

(11,’RAM’, 30000,10),

(22,’RAJ’,20000,20)

Like in SQL Server 2005 we need not to write multiple INSERT statements for multiple records. With in single INSERT statement we can insert multiple records into the table at a time

4.MERGE Statement

It is one of the DML statement placed newly in SQL Server 2008

Merge Statement is a combination statement that can perform INSERT, UPDATE, DELETE statements based on whether rows that match the selection criteria exist in the target table or not.

This Merge statement takes Target and Source Tables, When Source rows are matched with Target it will update the rows of source table into target table otherwise it will insert the rows of source table into target table. Always merge statement takes a temp table as its source and base table as its target table. Using this Merge statement  we can combine the rows of a source table to target table.

Syntax:

MERGE Target_Table as target

USING Source_table  as source

ON condition

WHEN MATCHED THEN

UPDATE

SET column=source.column,………..

WHEN TARGET NOT MATCHED THEN

INSERT VALUES(source.column1,source.column2,…………)

4.     New Data Types In 2008

SQL Server 2008 provides new data types that support storage, manipulation and querying of new forms of data. Some, like the date data type which stores a simple date with out the time component. Others like the geometry data type, which allows storage and querying of spatial data, have only recently addressed.

Date and Time Data Types

SQL Server supports  four brand new date and time data types, which includes

1. date
2. time
3. datetime2
4. datetimeoffset

The date data type finally allows us to store date only without the time component. It can also store a much larger range of dates than datetime and smalldatetime data types.

The date data type can handle dates from January 1, 1 CE(0001-01-01) to December 31, 9999 CE(9999-12-31)

Ex:

DECLARE @d1 date=’0014-08-19’

DECLARE @d2 date=’1983-02-26’

SELECT @d1 as DATE1,@d2 as DATE2

DATEDIFF(YEAR,@d1,@d2) as YEARDIFFERENCE

In contrast to the date data type, the time data type lets you to store time only data.

The range for the time data type is defined on 24 hour clock, from 00:00:00.0000000 through 23:59:59.9999999 with user defined fractional second precision of up to seven digits.

Ex:

DECLARE @start_time time(1)=’06:25:19.1’  --1 digit fractional precision

DECLARE @end_time time=’18:25:19.1234567’  --default fractional precision

SELECT @start_time as STARTTIME, @end_time as ENDTIME

DATEADD(HOUR, 6, @start_time) as STARTTIMEPLUS,

DATEDIFF(HOUR,@start_time,@end_time) as ENDSTARTDIFF

The cleverly named datetime2 data type is an extension to the standard datetime data type. The datetime2 combines the benefits of the new date and time data types, giving you the wide date range of the date data type and the greater fractional second precision of the time data type.

Ex:

DECLARE @start_dt2 datetime2=’1972-07-06T07:13:28.8235670’

DECLARE @end_dt2 datetime2=’2009-12-14T03:14:13.2349872’

SELECT @start_dt2 as start_dt2, @end_dt2 as end_dt2

The new datetimeoffset data type builds on datetime2 by adding the ability to store offsets relative to the International Telecommunication Union(ITU) standard for Coordinated  Universal Time(UTC) with your date and time data type.

5. The Hierarchyid  Data Type

The hierarchyid data type offers to represent hierarchical data in the database. This new data type offers built-in support for representing your hierarchical data using one of the simplest models available: materialized paths

Hierarchyid Data Type Methods

Method	Description
GetAncestor(n)	Retrieves the nth ancestor of the hierarchyid node instance
GetDescendent(n)	Retrieves the nth descendent of the hierarchyid node instance
GetLevel()	Gets the level of the hierarchyid node instance in the hierarchy
GetRoot()	Gets the root node of the hierarchyid  instance in the hierarchy
IsDescendent(node)	Returns 1 if a specified node is descendent
Parse(String)	Converts the given canonical string, in forward slash- separated format to a hierarchyid path.
Reparent(old_root,new_root)	Reparents a node by moving nodes from old_root to new_root
ToString()	Converts a hierarchyid instance to a canonical forward slash-separated string representation

5.      The Spatial Data Type

SQL Server 2008 includes two new data types for storing, querying, and manipulating spatial data.

·         Geometry

·         Geography

Geometry data type is designed to represent flat-earth, or Euclidean, spatial data.

Geography data type supports round earth or ellipsoidal, spatial data.

Conclusion

You can get updates for books on line (BOL) of 2008 at

www.microsoft.com/sql/default.mspx

The online version of SQL Server 2008 is available at

http://msdn2.microsoft.com/en-us/library/bb543165(sql:100).aspx

You can get the latest BOL updates from the Microsoft update site

http://update.microsoft.com/microsoftupdate