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
New and Enhanced Subsystems
Native support for XML is another important development enhancement. SQL Server 2005 provides this support through the new XML data type and Web services. Other subsystem changes that aid development are found in SQL Server Service Broker and Notification Services.
XML and Web services. At a high level, SQL Server 2005 provides a new level of unified storage for XML and relational data, through the use of the new native XML data type. This data type provides support for native XML queries and strong data typing by associating the XML data type to an XML Schema Definition (XSD). XML support is tightly integrated with the SQL Server 2005 relational database engine and provides support for triggers on and replication, bulk load, and indexing of the XML data contained in the XML data type.
You can use the XML data type as a column in a table or as a variable or parameter in a stored procedure. You can use the type to store both typed and untyped data. When the data stored in an XML column has no XSD schema, the data is considered untyped. When an associated XSD schema exists, SQL Server will check the schema to make sure that the data store complies with the schema definition. In all cases, SQL Server 2005 checks data stored in the XML data type to ensure that the XML document is well formed. If the data isn't well formed, SQL Server will raise an error and won't store the data. The following example illustrates the creation of a table that uses the XML data type:
CREATE TABLE XmlData( XmlID int NOT NULL, XmlData xml NOT NULL)
The XML data type supports a maximum of 2GB of storage—the same amount as SQL Server allows for BLOB storage. The data's size and usage can have a big impact on the performance that the system can achieve while querying the XML data. To improve the performance of XML queries, SQL Server 2005 lets you create indexes over the columns that have the XML data type (although a clustered primary key must exist for the table). In addition, the OPENXML statement, which provides a rowset view over an XML document, has been enhanced to include support for the new native XML data types.
T-SQL in SQL Server 2005 also supports the XQuery language subset, which is based on the standard XPath query language and is used to query structured or semi-structured XML data. You can combine XQuery with T-SQL to query the data in an XML data type. The following example selectively retrieves the contents of an XML variable:
DECLARE @x xml
SET @x = '<ROOT><ID1>111</ID1>
<ID2>2</ID2></ROOT>'
SELECT @x.query('/ROOT/ID2')
Another important new XML-related feature is native HTTP Simple Object Access Protocol (SOAP) support. This feature lets SQL Server directly respond to the HTTP SOAP requests that Web services issue, without requiring a Microsoft IIS system as an intermediary. You can use this native HTTP SOAP support to create Web services that run on SQL Server 2005 and that can execute T-SQL batches, stored procedures, and user-defined functions. Like CLR support, this feature is turned off by default for security reasons. To enable HTTP support, you must first create an HTTP endpoint, as the code in Listing 4 shows.
Service Broker. An all new application-development subsystem, SQL Server 2005 Service Broker provides a framework for developing distributed, asynchronous line-of-business applications. Many other highly scalable applications—OS I/O subsystems, Web servers, even the SQL Server database engine's internal operations—support asynchronous queuing. Such support is an important factor for scalability because it lets an application respond to more requests than the platform might be able to handle physically. Service Broker's new asynchronous-queuing capability is built directly into the SQL Server engine and is fully transactional. Transactions can incorporate queued events and can be both committed and rolled back. You can access the Service Broker by using a set of new T-SQL statements including CREATE CONTRACT, CREATE QUEUE, CREATE MESSAGE TYPE, BEGIN DIALOG, SEND, and RECEIVE. You can find more information about the new Service Broker in "Scoping Out Service Broker," April 2004, InstantDoc ID 41887.
Notification Services. Notification Services began as a Web download for SQL Server 2000; SQL Server 2005 incorporates the feature into the core product. Notification Services lets businesses build rich notification applications that deliver personalized and timely information—such as stock market alerts, news subscriptions, package delivery alerts, and airline ticket prices—to any device. The feature is a software layer that connects an information source (i.e., event) and the intended recipient of that information. Notification Services applications consist of three basic components: events, subscriptions, and notifications. The application monitors certain predefined events and can filter and route information about those events to a variety of target devices, using a personalized delivery schedule. You can find more information about Notification Services at http://msdn.microsoft.com/sql/sqlwarehouse/notificationservices/default.aspx.
Client-side Development with ADO.NET 2.0
SQL Server 2005 provides important improvements for client-side coding, in the form of the new ADO.NET 2.0. As ADO.NET continues to mature, we're finally seeing features that were missing in its earlier version. Plus, ADO.NET 2.0 includes several brand new features, such as support for asynchronous queries, multiple active result sets (MARS), and a common connection model.
Asynchronous queries. Support for asynchronous queries was present in COM-based ADO but was missing in the earlier releases of ADO.NET. Asynchronous queries let client applications submit queries without blocking the application. New ADO.NET asynchronous support in the mid-tier layer of applications lets server applications issue multiple database requests on different threads without blocking the threads. The implementation is the same as other asynchronous operations in the .NET Framework. Start an asynchronous operation by using the object's BEGINxxx method and end it by using the ENDxxx method. Use the IAsyncResult object to check the completion status of the command. Listing 5 shows an example of ADO.NET 2.0's asynchronous support, which will also work with earlier versions of SQL Server, including SQL Server 2000 and SQL Server 7.0.
MARS. Earlier versions of ADO.NET were limited to one active result set per connection. COM-based ADO and OLE DB had a feature that let the application process multiple results sets, but under the hood that feature was actually spawning new connections to process the additional commands. ADO.NET 2.0's MARS support (which works only with SQL Server 2005) gives you the ability to execute multiple active commands on a single connection. MARS lets you open a connection to the database, open the first command and process some results, open the second command and process results, then go back to the first command and process more results—freely switching back and forth between the active commands. There's no blocking between the commands, and both commands share a single connection to the database. Listing 6 shows some sample code that uses MARS.
Common connection model. One of the problems with ADO.NET 1.0 was that you needed to use a specific provider to connect to a specific target database platform. For example, the SqlClient could connect only to SQL Server systems, not to Oracle systems. Likewise, the OracleClient could connect only to Oracle systems, not to SQL Server systems. Although you could build your code to load different database providers on the fly, doing so wasn't elegant, easy, or convenient. ADO.NET 2.0 solves this problem by adding a new Provider Factory capability that can instantiate the appropriate provider at runtime. The new Provider Factory classes are in the System.Data.Common namespace; the code in Listing 7 shows how to use them.
There are plenty more highpoints in ADO .NET 2.0. For instance, ADO.NET 2.0 supports all the new SQL Server 2005 data types, as well as a new Bulk Import and client failover. You can find more information about the new version at http://msdn.microsoft.com/library/en-us/dnvs05/html/ado2featurematrix.asp, or by reading "ADO.NET 2.0," February 2005, InstantDoc ID 44762.
Keeps Getting Better
It should be no surprise that SQL Server 2005 is chock full of new development features, and we've just covered the tip of the iceberg. Microsoft has also added an XML/A query language for Analysis Services, a System Management Objects (SMO) management API, and a SQL Server SQLCMD scripting shell—to name just a few. For more information about SQL Server 2005, or to download an evaluation copy, visit http://www.microsoft.com/sql.
