SQL Server 2008 – Some Cool Features for Database Developer

This is an overview of four features in SQL Server 2008 that I think are neat and will be important to both developers and DBAs. For a comprehensive feature list, go to Microsoft's SQL Server 2008 Product Overview page.

1. Table variable parameters

For a long time, I have wished that developers could pass a table variable as a parameter to stored procedures. With the advent of SQL Server 2005, XML variables can be passed into procedures and parsed out with XQUERY inside the procedure to form a table. However, with the ability to pass a table variable in SQL Server 2008, developers will not have to be fully XQuery literate to pass a table of data into or out from a stored procedure. In my opinion, this will be one of the more integral tools for the developer in SQL Server 2008.

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

Note: You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

2. MERGE Statement

The new MERGE operator streamlines the process of populating a data warehouse from a source database. For example, rows that get updated in the source database will probably already exist in the data warehouse but rows that are inserted into the source database will not already exist in the data warehouse. The MERGE statement distinguishes between the new and updated rows from the source database so that the appropriate action (insert or update) can be performed against the data warehouse in one single call.

3. Grouping Sets
Use GROUPING SETS to obtain results similar to those generated by using CUBE and ROLLUP, however GROUPING SETS is more flexible, offers better performance, and is ANSI SQL 2006 compliant. GROUPING SETS enables the GROUP BY clause to generate multiple grouped aggregations in a single result set. It is equivalent to using UNION ALL to return a result set from multiple SELECT statements, each of which has a GROUP BY clause.