SQL Server 2008 T-SQL Improvements

Article
03/24/2009

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 https://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 https://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 https://msdn.microsoft.com/en-us/library/bb522495.aspx
Grouping set equivalents: https://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 https://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 https://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 https://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: https://msdn.microsoft.com/en-us/library/cc721269.aspx
Books Online: https://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 https://msdn.microsoft.com/en-us/library/cc879300.aspx

SQL Server 2008 T-SQL Improvements

Additional resources