SQL Server 2008’s T-SQL Features

Whenever a new version of SQL Server is released, there’s a lot of talk about the big new features. But in this article, rather than focusing on SQL Server 2008’s big features, I’ll cover smaller improvements that you might have overlooked. I’ll discuss the enhanced VALUES clause, support for the ISO week system, and new conversion options between binary and character types.

SQL Server 2008 introduces support for a row value constructor (also known as a table value constructor). Using the VALUES clause, you can now represent more than one row. The obvious scenario in which you might want to use this option is to use a single INSERT statement to insert multiple rows into a table rather than needing to use multiple INSERT statements. To demonstrate this capability, first run the code in Listing 1 to create a table called Orders in the tempdb database (for test purposes).

The following code demonstrates how to use the enhanced VALUES clause with a single INSERT statement to insert 6 rows:

INSERT INTO dbo.Orders

  (orderid, orderdate, empid, custid)

VALUES

  (10001, '20090212', 3, 'A'),

  (10002, '20090212', 5, 'B'),

  (10003, '20090213', 4, 'B'),

  (10004, '20090214', 1, 'A'),

  (10005, '20090213', 1, 'C'),

  (10006, '20090215', 3, 'C');

As you can see, each pair of parentheses represents a single row, and you separate the rows with commas. Besides the obvious benefit of shortening your code, another benefit of using a single statement is that the operation is considered atomic. If any of the rows fails to enter the target table, the entire INSERT statement fails.

Prior to SQL Server 2008 you could achieve a similar capability by performing a UNION ALL operation between several SELECT statements based on constants, like so:

INSERT INTO dbo.Orders

  (orderid, orderdate, empid, custid)

            SELECT 10001, '20090212', 3, 'A'

  UNION ALL SELECT 10002, '20090212', 5, 'B'

  UNION ALL SELECT 10003, '20090213', 4, 'B'

  UNION ALL SELECT 10004, '20090214', 1, 'A'

  UNION ALL SELECT 10005, '20090213', 1, 'C'

  UNION ALL SELECT 10006, '20090215', 3, 'C';

In fact, the current implementation of the enhanced VALUES clause is internally handled like this UNION ALL solution—so don’t expect any performance improvements. One benefit is that the VALUES clause is standard, whereas the solution based on UNION ALL isn’t, because the queries don’t have FROM clauses. We can only hope that in a future version of SQL Server, the VALUES clause will also provide performance benefits over the existing technique.

Note that you’re not restricted to using a table value constructor only with an INSERT statement. You can also use it to construct a derived table that you query in an outer query’s FROM clause, like so:

SELECT *

FROM ( VALUES

         (10001, '20090212', 3, 'A'),

         (10002, '20090212', 5, 'B'),

         (10003, '20090213', 4, 'B'),

         (10004, '20090214', 1, 'A'),

         (10005, '20090213', 1, 'C'),

         (10006, '20090215', 3, 'C') )

     AS O(orderid, orderdate, empid, custid);

Table 1 shows this query’s output.

I find this capability useful whenever I need to create a small auxiliary table made of constants for the purposes of a query. Now you don’t need to create a temporary table or table variable, or use the nonstandard UNION ALL technique for this purpose. Still missing, however, is the ability to define a common table expression (CTE) based on a table value constructor. For now, the technique works only with derived tables.