SQL Server 2005/2008 what's new, part 1

SQL Saturday 53, takes place in 47 days. I will be presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

Common Table Expressions

I like to come out swinging and Common Table Expressions, CTE, introduced with SQL Server 2005 are worth the price of admission! At their simplest, they're nothing more than syntactic sugar but don't dismiss them as such. There is very little you can do with CTEs that you couldn't do with temporary tables, derived tables, table variables and what have you. The biggest difference developing queries using CTEs versus the Tumbling data anti-pattern is that with a Common Table Expression the compiler can have a chance to optimize your query despite being separated into logic sub tables. Think of them as single use tables with no explicit cleanup required.

Syntax

The syntax is quite simple, see books online for the BNF. The following contrived query builds up data to define the states that border Missouri.

;
WITH BORDER_STATES (state_name, abbreviation) AS
(
    SELECT 'IOWA', 'IA'
    UNION ALL SELECT 'NEBRASKA', 'NE'
    UNION ALL SELECT 'OKLAHOMA', 'OK'
    UNION ALL SELECT 'KANSAS', 'KS'
    UNION ALL SELECT 'ILLINOIS', 'IL'
    UNION ALL SELECT 'KENTUCKY', 'KY'
    UNION ALL SELECT 'TENNESSEE', 'TN'
)
, BEST_STATE AS
(
    SELECT 'MISSOURI' AS state_name, 'MO' AS state_abbreviation
)
, JOINED AS
(
    SELECT M.*, 1 AS state_rank FROM BEST_STATE M
    UNION
    SELECT BS.*, 2 AS state_rank FROM BORDER_STATES BS
)
SELECT * FROM JOINED

What to notice

As a helpful suggestion, preface all your CTEs with a semi-colon on the preceding line. If they are the only statement in a batch, then it doesn't matter. Otherwise, you'll encounter "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." BORDER_STATES is an in memory table containing the 8 states that directly border Missouri. Notice that I explicitly specify the column names when I defined the table. BEST_STATE is also an in memory table but here I chose to define the column names within the query. JOINED shows I can reference the tables immediately after defining them. Finally, I reference JOINED from outside the CTE by grabbing all of the data.

What's better, it depends. Besides that hoary chestnut, I find myself defining columns if I am building up some static data values like I do in BORDER_STATES. Otherwise, I typically just alias my columns as I query them like I do with BEST_STATE.

Finally, you should notice that the use of SELECT * leads to puppies being slaughtered. I read it on a blog so it must be true.

Availability

SQL Server 2005+