TSQL New Features in SQL Server 2008

  • Article

Briefly, here are the new T-SQL programmability enhancements in SQL Server 2008:

1. Data Types

a. New Date/time types (date, time, datetime2, datetimeoffset)
b. New table types (i.e. CREATE TYPE myT AS table (a int, b varchar(100)) )

2. SQL language

a. MERGE statement
b. GROUPING SET

3. T-SQL procedure language

a. Table valued parameter (TVP) which is supported together with the new table types
b. Insert multiple rows through VALUE clause of a single INSERT statement

(i.e. INSERT INTO contacts VALUES ('John Doe', '425-333-5321'), ('Jane Doe', '206-123-4567'), ('John Smith', '650-434-7869') )

c. Assignment operators: +=, -=, *=, /=

(i.e. UPDATE Inventory SET quantity += s.quantity FROM Inventory AS i INNER JOIN Sales AS s ON i.id = s.id)

d. Variable initialization during declaration

(i.e. DECLAER @v int = 5; DECLARE @v1 varchar(10) = ‘xxxxx’; )

e. CAST/CONVERT binary data to hex string literals (i.e. 0xA1BEFE)

(i.e. select convert(char(4), col1_of_type_binary,1), .... from t1 ...... select ...... from t1, t2 where convert(char(4), t1.col1_of_type_binary,1) = t2.col1_of_type_char)

 f. Object dependency with new sys catalog and dmvs

i. sys.sql_expression_dependencies

ii. sys.dm_sql_referenced_entities

iii. sys.dm_sql_referencing_entities

g. DDL trigger now can be applied for all DDL operations such as:

i. Stored procedures like sp_rename

ii. Language DDL like full-text, security DDL

4. New windows collations are added to align with Windows Vista

5. Beyond relational

a. New HierarchyID data type and functionality for manipulating/operating/querying hierarchy data efficiently

b. Sparse columns and column set. For example:

// Create a sparse column set

Create Table Products (Id int, Type nvarchar(16)...m Resolution int SPARSE, ZoomLenght int SPARSE, Properties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 c. Filter index. For example:

// Create a Filtered Inddexes

Create Index ZoomIdx on Products (zoomlength) where type = 'Camera';

d. Spatial data types and functionalities