SQL Server Magazine

SQL Server 2008 offers so many important T-SQL enhancements,
I ran out of space last month describing them (see “T-SQL Enhancements in
SQL Server 2008,” InstantDoc ID 99416). Now let’s look at more T-SQL enhancements you might find useful.

1. Table-Valued Parameters
If you’ve ever needed to pass a large number of parameters to a stored procedure or function, you’ll appreciate SQL Server 2008’s support for table-valued parameters.
As you can see in Listing 1, I created a new Table Type called MyTableType. Next, I created a stored procedure that uses a table as a parameter. Last, you can see the new MyTableType variable that was populated and passed to the usp_MyProc stored procedure.

2. Enhanced CONVERT Function
The CONVERT function now supports multiple conversion styles, giving you more
control over the output of the function. I’ve used the new styles in the following example:

SELECT CONVERT(char(8),  0x4D69636861656C, 0) AS ‘Style 0’ SELECT CONVERT(char(8),  
0x4D69636861656C, 1) AS ‘Style 1’ SELECT CONVERT(char(8), 0x4D69636861656C, 2) AS ‘Style 2’

3. GROUPING SETS Operator
The new GROUPING SETS operator lets you specify multiple groupings of data
(or grouping sets) in a single query and gain more control over the aggregations the query returns.

In Listing 2, I used a SELECT statement to return three columns from the
Sales.SalesOrderHeader table and the Sales.Store table, aggregating the total
for the TotalDue column. The GROUPING SETS clause creates three grouping sets:
results by Name and DueDate, results by Name, and the entire result set.

End of Article