T-SQL Enhancements

With all the new .NET-related features, you may wonder if Microsoft is planning to drop support for T-SQL, but that’s definitely not the case. T-SQL is still the best language to use for raw data access, and as you might have noticed, the T-SQL syntax was used in the previous examples, so rest assured that T-SQL will be around for the foreseeable future. In fact, Microsoft has made a number of important enhancements to T-SQL that you’ll learn about in this section of the chapter.

Top Enhancements

In SQL Server 2000, you were forced to use a constant value in conjunction with the TOP clause. In other words, you could select the TOP 5 or the TOP 10 rows, where the value of 5 or 10 was a constant. With SQL Server 2005, the TOP function now enables the use of an expression in conjunction with the TOP clause. An expression can be any allowed T-SQL expression, including a variable or a scalar subquery. The TOP clause is also supported in the INSERT, UPDATE, and DELETE statements. This gives the TOP clause a great deal more flexibility than ever before. An example of using the new TOP clause is shown here:

USE AdventureWorks DECLARE @MyTop INT SET @MyTop = 15 SELECT TOP (@MyTop) CustomerID, SalesPerson FROM Sales.Customer

Common Table Expressions (CTE)

Another new T-SQL feature is support for common table expressions (CTEs). CTEs are a lot like views; however, they are embedded in a query. The main reason Microsoft introduced CTEs to SQL Server 2005 is to provide a mechanism for handling recursive queries. Recursion is achieved by the fact that a CTE is allowed to refer to itself. To avoid the possibility of overwhelming the system with a poorly constructed recursive query, SQL Server implements a server-wide limit on the maximum level of recursion allowed, with a default maximum of 100 levels. A CTE is implemented as part of the WITH keyword and can be used with SELECT, INSERT, UPDATE, and DELETE statements. To implement recursive queries using the new CTE, you must use a special syntax, as shown in the simple code example that follows. This example performs a simple recursive query using the HumanResources.Employee table in the example AdventureWorks database:

USE AdventureWorks WITH EmployeeChart(EmployeeID, ManagerID, Title) AS (SELECT EmployeeID, ManagerID, Title  FROM HumanResources.Employee  WHERE EmployeeID = 3  UNION ALL SELECT L2.EmployeeID, L2. ManagerID, L2.Title  FROM HumanResources.Employee AS L2  JOIN EmployeeChart   ON L2.ManagerID = EmployeeChart.EmployeeID) SELECT * from EmployeeChart

To use a CTE, you first write a WITH clause, which you use to name the CTE and specify the columns to bind to a SELECT statement. There must be a semicolon in front of the WITH keyword if it is not the first statement in a batch. The first SELECT statement is called the anchor member, and it must not refer to itself. In this case, it retrieves the EmployeeID, ManagerID, and Title columns from the AdventureWorks Employee table. The second SELECT statement references the CTE and is called the recursive member. In this case, it retrieves the same columns and is joined to the anchor member on the ManagerID column. You can see the results of this CTE in the following listing:

EmployeeID  ManagerID   Title ----------- ----------- -------------------------------------------------- 3           12          Engineering Manager 4           3           Senior Tool Designer 9           3           Design Engineer 11          3           Design Engineer 158         3           Research and Development Manager 263         3           Senior Tool Designer 267         3           Senior Design Engineer 270         3           Design Engineer 5           263         Tool Designer 265         263         Tool Designer 79          158         Research and Development Engineer 114         158         Research and Development Engineer 217         158         Research and Development Manager (13 row(s) affected) 

PIVOT and UNPIVOT

The addition of the PIVOT and UNPIVOT relational operators is another new feature found in SQL Server 2005’s T-SQL. The new PIVOT and UNPIVOT operators are most useful for OLAP scenarios where you’re dealing with tabular data rather than relational data. The PIVOT operator transforms a set of rows into columns. As you might expect, the UNPIVOT operator reverses the PIVOT operator, transforming the pivoted columns back into rows. However, depending on the situation, the UNPIVOT operation may not exactly reverse the PIVOT operation. This situation occurs because the PIVOT operation is often set up such that it will omit certain values. If a value is omitted during the PIVOT operation, it obviously cannot be unpivoted. Therefore, the UNPIVOT operator doesn’t always result in an exact mirror image of the original pivot condition.

You can see how the PIVOT operator works in the following listings. Given a simple select on a file named OrderSum, you can see a set of orders for a CustomerID of 1, where each order has an associated year.

OrderId     CustomerID  OrderYear ----------- ----------- ----------- 100         1           2000 101         1           2000 102         1           2000 103         1           2001 104         1           2001 105         1           2002 106         1           2003 107         1           2004 (8 row(s) affected)

Using SQL Server 2005’s new PIVOT operator, you can transform this result set, which lists each year vertically, into a result set that lists the years horizontally for each customer and sums up the number of orders for each year. The sample PIVOT operation is shown in the following listing:

SELECT * FROM OrderSum  PIVOT (COUNT(OrderID)    FOR OrderYear IN([2000], [2001], [2002], [2003], [2004]))    AS P  WHERE CustomerID = 1 

Here, the PIVOT operation is used with the SELECT statement to create a new result set. The first value of the pivot operator identifies the value that will be placed in the pivot column. In this example, the COUNT(OrderID) aggregation sums up the number of orders for each pivot value. The FOR keyword identifies the column whose values will be pivoted. This example shows the pivot operation being performed on the OrderYear column. The values identified by the IN keyword list are the values from the pivoted column that will be used as column headings. You can see the pivoted result set in the following listing:

CustomerID  2000        2001        2002        2003        2004 ----------- ----------- ----------- ----------- ----------- ----------- 1           3           2           1           1           1 Warning: Null value is eliminated by an aggregate or other SET operation. (1 row(s) affected)

DDL Triggers

Earlier versions of SQL Server only allowed triggers to be used with data manipulation events such as inserting or updating a row. SQL Server 2005 extends this by allowing triggers to be placed on DDL events such as creating and dropping database objects such as tables, views, procedures, and logins. DDL triggers can be associated with CREATE, ALTER, and DROP statements. This enables the DBA to place restrictions on the type of DDL operations that can be performed in a given database, or you can use these triggers to send notification messages regarding important schema changes that take place in the database. The following example shows how to add a DDL trigger named NoTableUpdate to the DROP Table and ALTER Table DDL statements:

CREATE TRIGGER NoTableUpdate ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'DROP TABLE and ALTER TABLE statement are not allowed' ROLLBACK

Here, you can see how the new DDL trigger can be used to restrict the use of the DROP TABLE and ALTER TABLE statements. If an ALTER TABLE or DROP TABLE statement is issued, the NoTableUpdate trigger will print an error message and roll back the attempted DDL operation. An attempt to issue an ALTER TABLE statement in the database containing the NoTableUpdate trigger is shown here:

DROP TABLE and ALTER TABLE statement are not allowed .Net SqlClient Data Provider: Msg 3609, Level 16, State 2, Line 1 Transaction ended in trigger. Batch has been aborted. 

To make alterations to the tables in a database after this trigger is in place, you will first need to drop the DDL trigger.

DML Output

Another new T-SQL feature found in SQL Server 2005 is the ability to produce output from T-SQL INSERT, UPDATE, and DELETE DML statements. The new Output clause returns the modified data. For instance, the following DELETE Statement removes all of the rows from the OrderSum table:

DECLARE @MyOrderSumTVar TABLE(     OrderID int,     CustomerID int,     OrderYear int); DELETE FROM OrderSum OUTPUT DELETED.* INTO @MyOrderSumTVar

SELECT * FROM @MyOrderSumTVar

Note 

The OrderSum example table was created in an earlier example in this chapter.

Here the OUTPUT DELETED.* clause specifies that all deleted rows will be output. With earlier versions of SQL Server, you would just see the number of rows that were affected by the statement. You can see the results of the new T-SQL DML Output clause here:

 (8 row(s) affected) OrderID     CustomerID  OrderYear ----------- ----------- ----------- 100         1           2000 101         1           2000 102         1           2000 103         1           2001 104         1           2001 105         1           2002 106         1           2003 107         1           2004 (8 row(s) affected) 

WAITFOR

Another new T-SQL feature in SQL Server 2005 is enhanced support for the WAITFOR command. In previous versions of SQL Server, the WAITFOR command was able to wait for only a predefined time. With SQL Server 2005, the WAITFOR command is able to wait for the results of a RECEIVE statement. The primary reason behind this feature is to facilitate T-SQL programming support for the new queuing capabilities provided by the SQL Service Broker subsystem. (You can learn more about the new SQL Service Broker in Chapter 6.) The following listing shows how the new WAITFOR command can be used in conjunction with a RECEIVE statement:

WAITFOR (RECEIVE TOP (1) * FROM dbo.ServiceBrokerQueue)

New varchar(max) Data Type

The new varchar(max) data type provides an alternative to text/image data type. The new varchar(max) data type is an extension to the varchar, nvarchar and varbinary data types. Like the text, ntext, and image data types, the varchar(max) data type supports up to 2GB of data. However, unlike the existing text, ntext, and image data types, the varchar(max) data type can contain both character and binary data. Likewise, it provides no support for text pointers.

Microsoft introduced the new varchar(max) data type to make working with these large data types more like working with standard string data. This allows you to use the same programming model for varchar(max) data that you use to work with standard varchar data. All string functions work on varchar(max) data types, and the SUBSTRING functions can be used to read chunks of data. In addition, the T-SQL UPDATE statement has been enhanced to support updating chunks within a varchar(max) data type. You can create a column using the new varchar(max) data type, as shown here:

CREATE TABLE NewBLOB (     DataID INT IDENTITY NOT NULL,     BLOBData VARCHAR(MAX) NOT NULL )

Transaction Abort Handling

Another important advance embodied by T-SQL in SQL Server 2005 is improved transaction abort handling. With SQL Server 2005, a new Try/Catch model has been added to the transaction. The new Try/Catch structure enables transaction abort errors to be captured with no loss of the transaction context. With SQL Server 2000, although you can abort a transaction there’s no way to maintain the context of the transaction so that you can completely recover the aborted transaction. SQL Server 2005’s new Try/Catch transaction abort handling enables you to maintain the complete context of the aborted transaction, giving you the option of re-creating the transaction. The following code listing shows the basic T-SQL Try/Catch structure:

BEGIN TRY     <SQL Statements> END TRY BEGIN CATCH TRAN_ABORT     <SQL Statements> END CATCH

The transaction is attempted in the Try block. If the RAISERROR with TRAN_ABORT statement is issued in the Try block, control is transferred to the Catch block. Within the Catch block, the @@error variable can be evaluated to determine the error condition.

When to Use T-SQL Database Objects

T-SQL database objects are not being phased out in SQL Server 2005. In fact, T-SQL is still the best choice for objects that need to perform raw data access operations. For instance, if you have stored procedures whose main function is inserting, updating, or deleting rows of data, then these objects should be developed using T-SQL rather than one of the .NET languages.