TSQL New Features in SQL Server 2008


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

Comments (0)