aFour cool new features slated for SQL Server 2008

SQL Server 2008 (code named Katmai) is scheduled to launch in February of next year, and the code isn’t slated to be released to manufacturing until Q2 2008. But database gurus don’t have to wait to get a sense of what’s to come because Microsoft recently released SQL Server 2008 to the community for preview.

Some of the goals of the Katmai Community Technology Preview (CTP) 1 release and future CTP releases include easing the manageability of the database, more security availability, more high availability options, and scalability. These types of overall enhancements are typically included in every version of SQL Server, which is why this article skips over such high-level features.

This is an overview of four features in SQL Server 2008 that I think are neat and will be important to DBAs. (Of course, there’s no guarantee that Microsoft will include these features in the final release.) For a comprehensive feature list, go to Microsoft’s SQL Server 2008 Product Overview page.

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.

MERGE statement

The idea behind the MERGE statement is that the developer can construct TSQL data-manipulation language (DML) statements in which INSERT, UPDATE, or DELETE can occur in the same statement, based on different search conditions. I think this idea is very cool. The ability to complete multiple statements within one statement could potentially lead to less coding and increased performance.

In addition to this statement, another great feature has been added to the INSERT statement. In SQL Server 2008, the developer can issue multiple rows to be inserted without using a SELECT statement as the INSERT statement source. Instead, the VALUE clause of the INSERT statement can be used to specify sets of values separated by parentheses and commas.

Declarative Management Framework

The Declarative Management Framework gives DBAs a central location to manage database server policies for server management, object management, and security. I presume that some type application will be deployed with client tools that use this framework to manage your database server policies. Because it is a framework, you should be able to develop your own database policies through custom .NET code.

The potential for this type of tool is huge. It could possibly save DBAs a huge amount of time administering enterprise databases.

Change Data Capture

Change Data Capture makes database auditing easier to create and maintain. While DML auditing has been available via triggers in SQL Server for a few versions, and DDL auditing has been available since SQL Server 2005, the solutions had to be custom written by a developer with reasonably intimate details of the database’s underpinnings. With Change Data Capture, system stored procedures are used to mark which types of objects you want to audit, and the stored procedures take care of how the auditing occurs.

While setting up these auditing procedures is typically a one-time deal, business requirements change over time; it is usually easier to use system stored procedures to handle changes, plus it’s less error prone than changing your custom auditing code. Unfortunately, when you use Change Data Capture, you will lose the auditing flexibility that you currently have with your own auditing solution.