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)


  • 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

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.
  • 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 – 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

Comments (1)