SQL Server 2008 T-SQL Improvements

Here are some notes on "SQL Server 2008 T-SQL Improvements" I took while attending an advanced class on SQL Server taught by Paul Nielsen (from http://www.sqlserverbible.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

T-SQL Variables

• Initialize when declaring : DECLARE @x INT = 1
• Incrementing : SET  @X += 1  (also with other operators)

TVP

• How to send 100 rows from a client to the sql server as one transaction?
• bcp, SQLBulkCopy, XML, SQLDataAdapter.Update CSV in VARCHAR(MAX), ADO.NET transactions…
• In the past, to use a SP, you need multiple calls
• Need a good, clean, elegant way to pass lots of rows to a SP.
• Table Valued Parameters are like a table variable (no statistics, not logged)
• Used inside the receiving SP, TVPs are read only, scoped to SP
• Demo: create type, create SP with TVP – Declare as READONLY
• See http://msdn.microsoft.com/en-us/library/bb675163.aspx

Grouping sets

• Replaces WITH CUBE and WITH ROLLUP
• GROUP BY GROUPING SETS ((column1), (column2))
• GROUP BY GROUPING SETS ((column1, column2))
• GROUP BY GROUPING SET ((column1), (YEAR(column2)))
• GROUP BY ROLLUP((column1, column2))
• GROUP BY CUBE((column1, column2))
• Still using GROUPING(column) in the query to tell which set.
• These are ISO-compliant
• See http://msdn.microsoft.com/en-us/library/bb522495.aspx
• Grouping set equivalents: http://msdn.microsoft.com/en-us/library/bb510427.aspx

Row Constructors

• Example: Multiple rows in a single INSERT
• INSERT table (c1, c2) VALUES (‘a’,1), (‘b’,1), (‘c’,3), (‘d’,4);
• Careful – This is good for demos, but does not perform as well as multiple inserts. It’s a single transaction.
• Example: SELECT…FROM VALUES
• SELECT * FROM (VALUES (‘a’,1), (‘b’,1), (‘c’,3), (‘d’,4)) as T1 (c1, c2)
• See http://msdn.microsoft.com/en-us/library/bb386869.aspx

Composable SQL

• Also known as: SQL from output, Select from DML, Insert over Output
• Extension of SQL Server 2005 output clause
• SQL 2000: You could only see insert/deleted inside the trigger
• SQL 2005: Output clause to send to client, table, temp table, table variable
• SQL 2008: MERGE output, Output can be returned as a subquery
• Use case: Return inserted GUIDs, $action
• Use case: Two table insert: Insert -> Output -> Insert
• Check restrictions on DML on both input side and output side
• See http://msdn.microsoft.com/en-us/library/ms177564.aspx

Dependency Tracking

• Changes in sys.sql_expression_dependencies, sys.dm_sql_referenced_entities, sys.dm_sql_referencing_entities
• Typically combine with OBJECT_DEFINTION() 
• sp_depends is deprecated, better than using sys.depends
• See http://msdn.microsoft.com/en-us/library/ms345449.aspx

Integrated Full Text Search (iFTS)

• iFTS is integrated into SQL Server
• Full text indexes, stop lists (noise words) in database
• Supports FILESTREAM columns
• SQL integer PK becomes the iFTS DocID, no DocIDMap
• Benefits in Performance, Manageability, Transparency
• White paper: http://msdn.microsoft.com/en-us/library/cc721269.aspx
• Books Online: http://msdn.microsoft.com/en-us/library/ms142571.aspx

iFTS - Using

• Setup with sp_fulltext_services
• Table Scan: SELECT… WHERE column like ‘%text%’
• iFTS: SELECT… WHERE CONTAINS (table.column, ‘text’)
• iFTS: SELECT… WHERE FREETEXT(table.column, ‘text’)
• Inside text: And, Near, IsAbout Weigh, Formsof Inflection
• JOIN with ContainsTable to FreeTextTable
• See http://msdn.microsoft.com/en-us/library/cc879300.aspx

Related information at
http://download.microsoft.com/download/7/a/b/7ab8283e-a3a0-4185-818b-ab7b1fc6300b/DAT326%20-%20T-SQL%20Enhancements%20in%20SQL%20Server%202008.ppt