Five years in the making, the new development features in SQL Server 2005 are really the accumulation of dozens of man-years worth of effort by the SQL Server and .NET Framework development teams. These new features are focused around several key areas: database object development using the Common Language Runtime (CLR), T-SQL enhancements, subsystems that facilitate application development, and client-side development enhancements. Let's take a quick look at the best of what SQL Server 2005 has in store.
CLR Database Objects
Arguably the most significant new feature in SQL Server 2005, the integration of the CLR with SQL Server extends the server product's capability in several important ways. The integration brings with it the ability to create database objects by using modern object-oriented languages such as VB.NET and C#. These objects—aggregates, stored procedures, triggers, user-defined functions, and user-defined types—support complex logic and can access external system resources through the .NET Framework.
To create CLR database objects, you write managed code, then compile it into a .NET assembly. Microsoft Visual Studio 2005 Professional Edition and later provides a new set of database project templates that assist in the creation of the objects. You can find more information about creating CLR objects in "Making Sense of the CLR," October 2004, InstantDoc 43680 and "Developing CLR-Based Stored Procedures," May 2004, InstantDoc 42208.
Because CLR database objects have the potential to access resources outside of the SQL Server database, Microsoft has added a new set of permissions to help secure them. Table 1 summarizes the options for these permissions. Furthermore, you must explicitly enable CLR support (which is turned off by default) by using the clr enabled server property.
The new objects are best suited for tasks that require complex logic, can be moved between the data tier and the business tier, or can benefit from the thousands of existing routines in the .NET Framework. One of the best uses for CLR stored procedures is as replacements for extended stored procedures. Bugs in extended stored procedures have the potential to affect the SQL Server database engine, but CLR objects are memory safe and contained by the .NET CLR. Listing 1 shows a simple CLR stored procedure.
T-SQL Enhancements
The inclusion of the CLR doesn't mean that Microsoft intends to drop support for T-SQL, which is still the best language to use for set-oriented data access. Microsoft has made a number of enhancements to T-SQL, including improvements to the TOP clause, support for common table expressions (CTEs), new PIVOT and UNPIVOT operators, the addition of Data Definition Language (DDL) triggers, and a new TRY-CATCH error-handling structure.
Enhanced TOP clause. SQL Server 2000 is limited to the use of a constant value in conjunction with the TOP clause, but in SQL Server 2005, the TOP function supports the use of an expression. Also, the INSERT, UPDATE and DELETE statements now support the TOP clause. The following statement shows an example of the new TOP clause using a variable:
DECLARE @MyLimit INT
SET @MyLimit = 11
SELECT TOP (@MyLimit) CustomerID
FROM Sales.Customer
Support for CTEs. A CTE provides a mechanism for handling recursive queries (among other uses) because a CTE can refer to itself. (To avoid the possibility of overwhelming the system because of a poorly constructed recursive query, SQL Server implements a server-wide limit on the maximum level of recursion allowed; the default is 100 levels.) You implement a CTE as a part of the WITH keyword and can use the CTE with SELECT, INSERT, UPDATE and DELETE statements. Listing 2 shows a sample statement that uses the CTE syntax to list employees along with their title and manager.
New Operators. The new PIVOT and UNPIVOT operators are most useful for OLAP scenarios in which you're dealing with tabular rather than relational data. The PIVOT operator transforms a set of rows into columns; the UNPIVOT operator reverses the pivoted columns back into rows. The sample statement that Listing 3 shows counts and pivots purchase orders per employee for three specified EmployeeIDs.
DDL triggers. Earlier versions of SQL Server let you use triggers only with Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. SQL Server 2005 lets you place triggers on DDL events, such as creating and dropping database objects (e.g., tables, views, stored procedures). You can use DDL triggers to place restrictions on the type of DDL operations that can be performed on a database object or to send notification when schema changes are performed. DDL triggers are associated with CREATE, ALTER, and DROP statements. The following example shows how you can use DDL triggers to prevent a table from being dropped:
CREATE TRIGGER MyTable
ON DATABASE FOR DROP_TABLE
AS
PRINT 'DROP TABLE is not allowed'
ROLLBACK
TRY_CATCH statement. The new TRY_CATCH error-handling statement lets you capture transaction-abort errors without losing the transaction context. SQL Server 2000 lets you abort a transaction but gives you no way to maintain the context, so you can't completely recover the aborted transaction programmatically. SQL Server 2005's new TRY_CATCH transaction-abort handling lets you maintain the complete context of the aborted transaction, giving you the option of recreating the transaction. The following statement gives an example of the TRY_CATCH statement:
BEGIN TRY
<SQL Statements>
END TRY
BEGIN CATCH TRAN_ABORT
<SQL Statements>
END CATCH
Prev. page
[1]
2
next page 
