Chapter 6. Transact-SQL Enhancements

IN THIS CHAPTER

• T-SQL Versus SQLCLR

• The TOP Operator

• The TABLESAMPLE Clause

• Common Table Expressions (CTEs)

• Large Object Data Type Enhancements

• T-SQL Error Handling

• DDL Triggers

• Event Notifications

• Snapshot Isolation

• Ranking Functions

• New Relational Operators: PIVOT, UNPIVOT, and APPLY

• The OUTPUT Clause

• The BULK Rowset Provider

• New Declarative Referential Integrity Actions

• Metadata Views and Dynamic Management Views

• Miscellaneous T-SQL Enhancements

Even though SQL Server 2005 supports writing stored procedure, functions, and triggers using .NET languages such as C#, Transact-SQL (T-SQL) will still be the primary language that database developers and DBAs will use to write scripts and procedural code. SQLCLR integration is provided as a supplement to help in writing business logic or complex computational tasks, or to perform functions that cannot easily be done by using T-SQL. SQLCLR is not a replacement for T-SQL. In fact, SQL Server 2005 introduces several new enhancements to the T-SQL language, as you will learn in this chapter.

The first section in this chapter compares .NET integration support with T-SQL, and the rest of the chapter details the new and improved T-SQL features.

T-SQL Versus SQLCLR

SQL Server has traditionally shipped with the ability to support one native programming language, TSQL. With the release of SQL Server 2005, a big enhancement to the SQL Server 2005 engine is the ability to host the .NET common language runtime (CLR). This enables the writing of procedural code, such as stored procedures, functions, and triggers, using any of the .NET languages, such as Visual Basic .NET or C#. The addition of CLR support in SQL Server 2005 does not mean that T-SQL is no longer supported or that T-SQL should no longer be used. It is important to recognize that SQL Server database applications should use T-SQL as much as possible. Database applications should take advantage of the set-oriented query processor and resort to procedural programming only for expressing logic that cannot be expressed within the query language. This remains true with CLR support in SQL Server. The CLR should not be used to write procedural code that can be expressed in a single SELECT statement.

You should think of the CLR as an alternative for logic that cannot be expressed in T-SQL. A good point of reference is that the CLR should be considered to replace extended stored procedures in SQL Server or to build user-defined functions that perform logic not easily performed in T-SQL. The CLR is a complement to T-SQL, not a replacement for it.

The SQLCLR code benefits from the large number of classes and functions available as part of the .NET Framework base class library. The .NET Framework class library is much richer than the built-in functions supported in T-SQL. In addition, CLR programming languages provide rich constructs, such as arrays and lists, that are lacking in T-SQL.

The SQLCLR code is compiled code, whereas T-SQL is an interpreted language. Therefore, SQLCLR code yields better performance in certain scenarios.

Database administrators have the ability to enable or disable CLR integration in SQL Server 2005. By default, it is disabled. A new sp_configure advanced option has been added to SQL Server 2005 to control this setting. The following script enables the SQLCLR integration:

--Show advanced options
EXEC sys.sp_configure 'Show Advanced Options', 1;
RECONFIGURE WITH OVERRIDE
GO
--You can check the current value of CLR before setting
EXEC sys.sp_configure 'clr enabled';
GO
--Change CLR configuration
EXEC sys.sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE
GO
--Check the updated value
EXEC sys.sp_configure 'clr enabled';
GO

You can also enable or disable CLR integration by using the SQL Server Surface Area Configuration tool. After the server is configured to allow CLR integration, the functions in a .NET assembly can be mapped to a SQL Server stored procedure, function, or trigger, which can then be called like any other T-SQL module. SQL Server verifies the clr enabled setting when a SQLCLR method is invoked and not when it is mapped to a T-SQL module. When a SQLCLR method is executed, if the clr enabled option is turned off, SQL Server raises an error.

When you have a .NET assembly that contains SQLCLR functions, the first step in using these functions from within T-SQL code is to import the .NET assembly by using the CREATE ASSEMBLY statement. Next, you use the AS EXTERNAL NAME clause with a CREATE PROCEDURE/FUNCTION/TRIGGER DDL statement to map a T-SQL stored procedure or function or trigger to the specified .NET method. Let’s assume that you have a SQLCLR .NET assembly called Test.dll, which contains a method that you want to use as a stored procedure from T-SQL code. Here is how you register the assembly, map the stored procedure, and invoke the SQLCLR code:

CREATE ASSEMBLY DotNetSQLServerSProc
FROM 'C:DevTest.dll'
WITH PERMISSION_SET = SAFE;
GO
--Create procedure and map it to a method in the assembly
CREATE PROCEDURE dbo.sqlclr_HelloWorld @Message NVARCHAR(255)
AS EXTERNAL NAME DotNetSQLServerSProc.StoredProcedures.PrintMessage;
GO
--Execute the stored procedure
EXEC dbo.sqlclr_HelloWorld N'Hello to the DotNet world!';
GO

The important things to note in this batch are the WITH PERMISSION_SET clause in the CREATE ASSEMBLY DDL statement and the AS EXTERNAL NAME clause in the CREATE PROCEDURE DDL statement. SQLCLR integration is discussed in great detail in Chapter 11, “SQL Server 2005 and .NET Integration.”

The following section describes the T-SQL enhancements introduced in SQL Server 2005.

The TOP Operator

Although the TOP operator has been around since SQL Server 7.0, in the past it only accepted a constant as the number of rows to return or as the percentage of the rows to return. The TOP statement in SQL Server 7.0 and SQL Server 2000 was also limited to SELECT statements. Thanks to improvements in the TOP operator for SQL Server 2005, the TOP statement can now accept variables and subqueries for the number of rows to return or the percentage of the rows to return. The TOP operator has also been enhanced so that it can be used with INSERT, UPDATE, and DELETE statements in addition to the SELECT statement.

Traditionally, developers utilized the TOP operator to reduce the number of rows returned by a query. When developers needed to page the results from a SQL Server query, they would either have to just return a static number of rows each time or build a dynamic query in which they could control the actual number of rows being returned by the query. The following query returns only 10 rows of data, and by changing the value of the variable, you could essentially page through a large result set:

USE AdventureWorks;
GO
DECLARE @startAfter INTEGER;
SET @startAfter = 0;
SELECT TOP (10) * FROM HumanResources.Employee
   WHERE EmployeeID > @startAfter
   ORDER BY EmployeeID;
GO

You could then return the result set to the client, and when you needed to page down, you would just reset the variable to the largest EmployeeID value (for example, 10) returned in the current set of data:

DECLARE @startAfter INTEGER;
SET @startAfter = 10;
SELECT TOP (10) * FROM HumanResources.Employee
   WHERE EmployeeID > @startAfter
   ORDER BY EmployeeID;
GO

Most of the time, this method is fine, but what if there were only two rows left in the table? Would you really want to incur an additional network trip to return only two rows? You could get around this issue with the use of dynamic SQL statements to change the number of rows returned by the query to include those two additional rows, as shown here:

DECLARE @strSQL NVARCHAR(1000);
DECLARE @intRows INTEGER;
DECLARE @startAfter INTEGER;
SET @intRows = 10 ;
SET @startAfter = 0;
SET @strSQL = 'SELECT TOP (' + CAST(@intRows AS VARCHAR(3)) + ') *
     FROM HumanResources.Employee
     WHERE EmployeeID > ' + CAST(@startAfter AS VARCHAR(3)) + '
     ORDER BY EmployeeID';
EXEC sys.sp_executesql @strSQL;
GO

For the next result set, if 12 rows should be returned, you can set @intRows to 12, and the dynamic SQL execution will take care of returning the requested number of rows. This way, you can prevent a third stored procedure call and an additional network trip.

With SQL Server 2005, the ability to pass an expression with the TOP operator enables the application to simply state how many rows of data it requires; this prevents the need for dynamic SQL and the problems associated with using dynamic SQL:

DECLARE @intRows INTEGER;
DECLARE @startAfter INTEGER;
SET @intRows = 12 ;
SET @startAfter = 10;
SELECT TOP (@intRows) * FROM HumanResources.Employee
   WHERE EmployeeID > @startAfter
   ORDER BY EmployeeID;
GO

You can still use the PERCENT keyword to return a percentage of rows instead of a fixed number of rows:

DECLARE @intPercRows INTEGER
DECLARE @intPage INTEGER
SET @intPercRows = 12
SET @intPage = 10
SELECT TOP (@intPercRows) PERCENT * FROM HumanResources.Employee
   WHERE EmployeeID > @intPage
   ORDER BY EmployeeID;
GO

In addition to passing an expression, you can also use a subquery to satisfy the TOP statement value requirement. Here is an example of passing a subquery with the TOP operator:

DECLARE @startAfter INTEGER;
SET @startAfter = 0;
SELECT TOP (SELECT COUNT(*)/11 FROM HumanResources.Employee) *
  FROM HumanResources.Employee
  WHERE EmployeeID > @startAfter
  ORDER BY EmployeeID;
GO

Considering that the HumanResources.Employee table has 290 rows, you can use this script to retrieve 11 batches of 26 rows, and the remaining 12th batch of 4 rows, by changing the value of the @startAfter variable.

The TABLESAMPLE Clause

The previous section illustrates one way of limiting a result set by using the TOP operator. SQL Server 2005 introduces a new clause, TABLESAMPLE, that you can use in a SELECT statement to restrict a result set to a randomly selected row. This clause is useful when you don’t necessarily need to process all the rows and when you don’t need the exact results—you just need to work on a sample of rows from a table that contains millions of rows. For instance, if a sales table contains a few million rows and you need to find out the approximate average order quantity, using TABLESAMPLE will yield better performance than processing all the rows in the table.

You can specify a number that indicates how many rows or what percentage of rows should be sampled. If you specify a number instead of a percentage, SQL Server calculates a percentage value based on the total number of rows and uses that percentage value for sampling. Let’s assume that this percentage number is 30. SQL Server will then return all the rows from 30% of the specified table’s data pages. If a table consists of a single page, either all rows on the page are returned or none of the rows are returned.

Let’s look at an example of using the TABLESAMPLE clause to find out the approximate average line total from the Sales.SalesOrderDetail table in the AdventureWorks sample database:

USE AdventureWorks;
GO
SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail;
GO
SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail
     TABLESAMPLE (30 PERCENT);
GO

The first SELECT statement finds the exact average value by processing all the rows in the table, and on my machine it returns 905.449206. If you execute this SELECT statement multiple times, you will notice that every time it returns a different value, which shows that SQL Server 2005 selects rows from a 30% set of random pages to calculate the average. You should get values such as 919.500875, 913.139024, 907.427679, 922.164805, and so on. If you want to get the same results every time, you can use the REPEATABLE clause and specify a seed value. As long as the same seed value is passed and the data is unchanged, you should get the same result:

SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail
     TABLESAMPLE (30 PERCENT) REPEATABLE(2);
GO

The ISO SQL-2003 standard includes two sampling methods: BERNOULLI and SYSTEM. SQL Server 2005 supports only the SYSTEM sampling method. The other sampling method might be supported in the future. You can be specific and provide the SYSTEM method as an algorithm name in the TABLESAMPLE statement, as shown here:

SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail
     TABLESAMPLE SYSTEM (50000 ROWS);
GO

This query also illustrates how you specify the number of rows instead of a percentage value.

Common Table Expressions (CTEs)

A new T-SQL enhancement in SQL Server 2005 that will have a dramatic effect on future queries is common table expressions (CTEs), which are defined in SQL-99. CTEs can be thought of as a type of derived table, but unlike with derived tables, the result set of CTEs can be defined once and used multiple times in the defining query. With this capability to define once and use it several times, CTEs can even reference themselves, making CTEs superior to other methods for defining hierarchies with a SQL Server query.

Here is the simplistic CTE syntax:

WITH <cte_alias>(<column_aliases>)
AS
(
      <cte_query>
)
SELECT * FROM <cte_alias>;

SQL Server 2005 supports two different types of CTEs: non-recursive and recursive. CTEs that do not have self-references are non-recursive, whereas recursive CTEs have selfreferences. Here is an example of simple non-recursive and recursive CTEs:

USE AdventureWorks;
GO
--Non-recursive CTE
WITH NonRecCTE (cnt) AS
(
    SELECT COUNT(EmployeeID) AS cnt
    FROM HumanResources.Employee
    GROUP BY ManagerID
)
SELECT AVG(cnt) AS AvgDirectReports
FROM NonRecCTE;

--Recursive CTE
WITH RecCTE(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
       FROM HumanResources.Employee
       WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
       FROM HumanResources.Employee e

       INNER JOIN RecCTE r
       ON e.ManagerID = r.EmployeeID
)
SELECT EmployeeID, ManagerID, EmployeeLevel
FROM RecCTE;
GO

In the non-recursive CTE query in this example, the inner query groups the employees by ManagerID and returns the count. The outer query in this non-recursive CTE then performs an average on this count column and returns a number indicating the average number of direct reports in the Employee table.

A recursive CTE always contains at least two queries combined using a UNION ALL clause. The first query, called the anchor member, is the starting point (usually the first row in the result set). In the second query, the called recursive member follows the UNION ALL clause, and in this query the CTE refers to itself. The preceding recursive CTE query returns the EmployeeID, the ManagerID, and the level of the employee in the hierarchy.

The most common problem with recursion is infinite loops. SQL Server solves this problem by defining a serverwide recursion level limit setting, called MAXRECURSION, which defaults to 100. You can also specify the MAXRECURSION hint in your outer query to limit the recursion level for that query.

A CTE can refer to another CTE, as illustrated in the following query:

WITH YearlyOrderAmtCTE(OrderYear, TotalAmount)
AS
(
     SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)
     FROM Sales.SalesOrderHeader AS H JOIN Sales.SalesOrderDetail AS D
          ON H.SalesOrderID = D.SalesOrderID
     GROUP BY YEAR(OrderDate)
),
SalesTrendCTE(OrderYear, Amount, AmtYearBefore, AmtDifference, DiffPerc)
AS
(
SELECT thisYear.OrderYear, thisYear.TotalAmount,
        lastYear.TotalAmount,
        thisYear.TotalAmount - lastYear.TotalAmount,
        (thisYear.TotalAmount/lastYear.TotalAmount - 1) * 100
FROM YearlyOrderAmtCTE AS thisYear
      LEFT OUTER JOIN YearlyOrderAmtCTE AS lastYear
      ON thisYear.OrderYear = lastYear.OrderYear + 1
)
SELECT * FROM SalesTrendCTE;
GO

This query defines two non-recursive CTEs. The first CTE, called YearlyOrderAmtCTE, groups the total sales by year. This CTE is then used in the second CTE, called SalesTrendCTE, and the outer or main query selects all the rows from SalesTrendCTE. The preceding query returns the following results:

OrderYear Amount          AmtYearBefore  AmtDifference  DiffPerc
--------- --------------- -------------- -------------- ---------
2001      11336135.376    NULL           NULL           NULL
2002      30859192.305    11336135.376   19523056.929   172.21
2003      42308575.2263   30859192.305   11449382.9213  37.10
2004      25869986.4061   42308575.2263  −16438588.8202 −38.86

Neither recursive nor non-recursive CTEs can include the following clauses in the defining, or inner, query for the CTE:

• COMPUTE or COMPUTE BY

• ORDER BY (except when a TOP clause is specified)

• INTO

• OPTION clause with query hints

• FOR XML

• FOR BROWSE

Recursive CTEs have the additional following restrictions:

• The FROM clause of the recursive member must refer only once to the CTE expression_name.

• The recursive member CTE_query_definitions does not allow SELECT DISTINCT, GROUP BY, HAVING, scalar aggregation, TOP, LEFT or RIGHT OUTER JOIN (INNER JOIN is allowed), functions with input or output parameters, or subqueries.

Let’s look at the power of CTEs, using a simple example. In this example, a report is created to show each manager, his or her direct reports, and the employees under each manager’s direct reports—kind of an organization chart:

WITH OrgChart
(FirstName, LastName, Title, ManagerID, EmployeeID, EmpLevel, SortValue)
AS
(

    SELECT ct.FirstName, ct.LastName, emp.Title, emp.ManagerID,
           emp.EmployeeID, 0 AS EmpLevel,
         CAST(emp.EmployeeID AS VARBINARY(900))
    FROM HumanResources.Employee emp INNER JOIN Person.Contact ct
            ON ct.ContactID = emp.ContactID
    WHERE emp.ManagerID IS NULL

    UNION ALL

    SELECT ct.FirstName, ct.LastName, emp.Title, emp.ManagerID,
           emp.EmployeeID, EmpLevel + 1,
           CAST(SortValue + CAST(emp.EmployeeID AS BINARY(4))
                 AS VARBINARY(900))
    FROM HumanResources.Employee emp JOIN Person.Contact AS ct
            ON ct.ContactID = emp.ContactID
         JOIN OrgChart org
            ON emp.ManagerID = org.EmployeeID
)
SELECT
   EmpLevel, REPLICATE(' ', EmpLevel) + FirstName + ' ' + LastName
   AS 'Employee Name', Title
FROM OrgChart
ORDER BY SortValue;
GO

The anchor member in this recursive CTE query finds out about an employee who does not have a manager (ManagerID IS NULL). Then the recursive member finds out the topmost manager’s direct reports, and then in recursion it finds out about employees reporting to direct reports. The outer SELECT statement uses the REPLICATE function and EmpLevel and SortValue columns to generate a formatted organization chart of employees. The query produces the following results (some rows have been omitted for brevity):

EmpLevel  Employee Name                   Title
--------  ------------------------------  ----------------------------
0         Ken Sánchez                     Chief Executive Officer
1           David Bradley                 Marketing Manager
2               Kevin Brown               Marketing Assistant
2               Sariya Harnpadoungsataya  Marketing Specialist
2               Mary Gibson               Marketing Specialist
2               Jill Williams             Marketing Specialist
2               Terry Eminhizer           Marketing Specialist
2               Wanida Benshoof           Marketing Assistant
2               John Wood                 Marketing Specialist
2               Mary Dempsey              Marketing Assistant
1           Terri Duffy                   Vice President of Engineering
2               Roberto Tamburello        Engineering Manager

Large Object Data Type Enhancements

Often when working with large strings in SQL Server 2000, developers were forced to use a text, image, or ntext data type. Most developers quickly realized that working with these large object data types was cumbersome. Some of the limitations with text, image, and ntext data types include are as follows:

• You cannot declare a variable of these types.

• Many string functions, such as LEFT, RIGHT, and so on, do not work with these types.

• These data types often require special handling, by using functions such as TEXTPTR and TEXTVALID and T-SQL statements such as WRITETEXT and UPDATETEXT.

SQL Server 2005 fixes this problem by introducing enhancements to the varchar, varbinary, and nvarchar data types to allow for increased storage. The max option now allows varchar, nvarchar, and varbinary to hold up to 2GB in this release. max may refer to a new maximum in a future release.

The types using the max specifier do not require any special handling, as do text, ntext, and image types. You can treat the max large object types as regular varchar, nvarchar, and varbinary types, and you can use them in joins, in subqueries, to order by, to group by, with distinct clauses, with aggregates, for comparison, for concatenation, and with string functions such as LEFT, RIGHT, SUBSTRING, and so on. You can define variables and parameters of large object types with the max specifier, and you can store up to 2GB of data in the variable or parameter.

Here is an example of using the max specifier in a table column and a variable declaration:

USE AdventureWorks;
GO

IF OBJECT_ID('HumanResources.EmployeeNotes') IS NOT NULL
   DROP TABLE HumanResources.EmployeeNotes;
GO
CREATE TABLE HumanResources.EmployeeNotes
(EmployeeID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
  Notes NVARCHAR(MAX));
GO

DECLARE @varNotes NVARCHAR(MAX)
SET @varNotes = N'New Hire 4/11/2005'
INSERT INTO HumanResources.EmployeeNotes SELECT @varNotes;
GO

SELECT * FROM HumanResources.EmployeeNotes ;
GO

This script illustrates declaring a column and a variable of type nvarchar(max).

Often when you store a large amount of data in a max type column, you might want to update just part of the column or variable data instead of completely replacing the value. The legacy large object types (text, ntext, and image) provided the UPDATETEXT T-SQL statement to change only a portion of data. For max types, SQL Server 2005 provides similar functionality via the .write(expression, @offset, @length) clause, which can be used in a SET statement for variables and in an UPDATE statement for a column, as illustrated in the following script:

UPDATE HumanResources.EmployeeNotes
SET Notes.write('; Orientation and training complete 4/13/2005', NULL, NULL);

SELECT * FROM HumanResources.EmployeeNotes ;
GO


------------------

DECLARE @varNotes NVARCHAR(MAX)
DECLARE @yearIndex INT

SELECT TOP(1) @varNotes = Notes FROM HumanResources.EmployeeNotes;
SET @yearIndex = CHARINDEX('2005', @varNotes)
SET @varNotes.write('2004', @yearIndex - 1, 4);
SET @yearIndex = CHARINDEX('2005', @varNotes)
SET @varNotes.write('2004', @yearIndex - 1, 4);
PRINT @varNotes;
GO

The first batch in this script runs an UPDATE statement and passes @offset and @length as NULL in the .write() clause, which indicates that you want to append the first parameter to the existing value. The second batch in this script illustrates updating parts of the nvarchar(max) variable data by using the .write() clause—in this case replacing 2005 with 2004 in the variable string.

T-SQL Error Handling

T-SQL developers often used the combination of the @@ERROR global variable and the RETURN or GOTO statement inside a batch or a stored procedure to handle errors. Because SQL Server clears or resets @@ERROR after every statement, in order to catch the error, it is required to check for @@ERROR immediately after the statement. SQL Server did not provide any other useful information besides @@ERROR on what went wrong.

SQL Server 2005 adopts the modern error-handling paradigm and introduces support for TRY...CATCH blocks. You can place multiple statements in a TRY block and catch any error in the CATCH block. Within the CATCH block, you can invoke functions such as ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE() to get more information on the error. Here’s an example:

USE AdventureWorks
GO
IF OBJECT_ID('Production.tblTry_Catch_Audit') IS NOT NULL
   DROP TABLE Production.tblTry_Catch_Audit;
GO
CREATE TABLE Production.tblTry_Catch_Audit
( ErrCode INT, ErrMessage NVARCHAR(4000),
  ErrUser NVARCHAR(100) DEFAULT SYSTEM_USER NOT NULL,
  ErrDate DATETIME DEFAULT GETDATE() NOT NULL);
GO

IF OBJECT_ID('Production.spTRY_CATCH') IS NOT NULL
   DROP PROCEDURE Production.spTRY_CATCH
GO
CREATE PROCEDURE Production.spTRY_CATCH (@ProductID INT)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @errorString  NVARCHAR(4000)
   DECLARE @rowcount     INTEGER

   BEGIN TRY
      DELETE FROM Production.Product
        WHERE ProductID = @ProductID;
   END TRY
   BEGIN CATCH
      SELECT @errorString = 'ERROR '
             + CONVERT(NVARCHAR(100), ERROR_NUMBER())
             + ' in procedure ''' + ERROR_PROCEDURE()
             + ''' while deleting the product.'
             + CHAR(13) + ERROR_MESSAGE();

      INSERT INTO tblTry_Catch_Audit (ErrCode, ErrMessage)
         VALUES(ERROR_NUMBER(), @errorString);

      RAISERROR(@errorString, 10, 1);
      RETURN ERROR_NUMBER();
   END CATCH
   RETURN 0
END;
GO

EXEC Production.spTRY_CATCH @ProductID = 1;
GO

SELECT * FROM Production.tblTry_Catch_Audit;
GO

In this example, deleting a product creates a referential integrity error, which is captured by the TRY...CATCH block. The CATCH block generates a more descriptive error string, saves that into an error audit table, and returns that, along with the actual error message and error number, to the caller.

You should keep in mind the following when writing TRY...CATCH blocks:

• The END TRY statement in a TRY...CATCH block must be immediately followed by a BEGIN CATCH statement. A syntax error is reported if you put anything between END TRY and BEGIN CATCH.

• A TRY...CATCH construct cannot span multiple batches.

• If the code in the CATCH block raises an error, the error is sent back to the caller, unless you have a nested TRY...CATCH block in the CATCH block itself. SQL Server 2005 allows nested TRY...CATCH blocks. You can have a TRY...CATCH block within another BEGIN TRY...END TRY block or BEGIN CATCH...END CATCH block.

• The GOTO statement can be used to jump within the same TRY or CATCH block or to jump out of a TRY or CATCH block, but it cannot be used to enter a TRY or CATCH block.

• TRY...CATCH blocks cannot be used to capture all T-SQL errors. For example, the TRY...CATCH block will not capture any syntax errors or any warning below severity level 11 or errors above severity level 20, and so on.

• If the code in a TRY block generates a trappable error, the control goes to the first line in the CATCH block. If you want to return the error information to the caller, you can use either SELECT, RAISERROR, or PRINT to do that, as illustrated in the preceding example.

DDL Triggers

The concept of triggers is not new in SQL Server 2005. Almost every RDBMS supports a procedural T-SQL code, called a trigger, that is invoked when a Data Modification Language (DML) statement, such as INSERT, UPDATE, or DELETE, is executed. SQL Server 2005 expands this support, however, and now you can have a procedural T-SQL code executed when a DDL statement, such as CREATE TABLE, ALTER TABLE, DROP TABLE, and so on, is executed.

A DDL trigger can be defined at the database level or at the server level. A database-level DDL trigger allows you to capture events such as CREATE/ALTER/DROP TABLE, VIEW, USER, ROLE, and other DDL statements. A server-level DDL trigger allows you to respond to events such as CREATE/ALTER/DROP LOGIN, CERTIFICATE, and other server-level DDL statements. The syntax to create a DDL trigger is similar to that for creating a DML trigger, except that you specify whether it is a database-scoped (the ON DATABASE clause) or a server-scoped (the ON ALL SERVER clause) DDL trigger.

Here is an example of a script that creates a database-level DDL trigger to audit and prevent dropping of a table:

USE AdventureWorks;
GO
IF OBJECT_ID('dbo.tblDDLActions') IS NOT NULL
   DROP TABLE dbo.tblDDLActions;
GO
CREATE TABLE dbo.tblDDLActions
(RecordID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
  Action XML,
  ActiveUser NVARCHAR(100) DEFAULT SYSTEM_USER,
  ActionDate DATETIME DEFAULT GETDATE());
GO

IF EXISTS (SELECT name FROM sys.triggers
           WHERE parent_class = 0 AND name = 'trgTest1')
   DROP TRIGGER trgTest1
   ON DATABASE;
GO

IF OBJECT_ID('dbo.tblTest') IS NOT NULL
   DROP TABLE dbo.tblTest;
GO

CREATE TRIGGER trgTest1
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
   DECLARE @eventData XML
   SET @eventData = EVENTDATA();
   ROLLBACK;
   SET NOCOUNT ON;
   INSERT INTO dbo.tblDDLActions (Action) VALUES (@eventData);
   PRINT 'Dropping a table is not allowed';END;
GO

--Test the trigger
CREATE TABLE dbo.tblTest(col1 int);
GO
BEGIN TRY
   DROP TABLE tblTest;
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE();
END CATCH
GO

SELECT * FROM dbo.tblDDLActions;
GO

--Clean up
DROP TRIGGER trgTest1 ON DATABASE;
DROP TABLE dbo.tblTest;
DROP TABLE dbo.tblDDLActions;

This script creates an audit table that is used to store details about who performed the DDL action and when, and what action (dropping a table in this case).

The EVENTDATA() function can be used inside a DDL trigger to find complete details about an action that led to the firing of the trigger. This function returns a value of xml data type. Here is an example of the XML document returned by the EVENTDATA() function for the DROP_TABLE database-level event:

<EVENT_INSTANCE>
    <EventType>DROP_TABLE</EventType>
    <PostTime>2005-02-25T16:36:12.313</PostTime>

    <SPID>51</SPID>
    <ServerName>DDGXPDECCTP</ServerName>
    <LoginName>DDGXPDarshan Singh</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>AdventureWorks</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>tblTest</ObjectName>
    <ObjectType>TABLE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                    QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
        <CommandText>DROP TABLE tblTest;</CommandText>
    </TSQLCommand>
</EVENT_INSTANCE>

You can save the XML returned by EVENTDATA() into a table, as is done in the preceding example, or directly execute XQuery queries on the value returned. XML data types and XQuery are discussed in detail in Chapter 10.

Here is an example of a server-level DDL trigger that prevents the creation of a SQL Server login. It allows the creation of Windows NT authenticated logins, but if it finds out that the login being created uses SQL authentication, it executes the ROLLBACK statement to abort the statement that fired the trigger:

IF EXISTS (SELECT name FROM sys.server_triggers
           WHERE parent_class = 100 AND name = 'trgTest2')
   DROP TRIGGER trgTest2
   ON ALL SERVER;
GO

CREATE TRIGGER trgTest2
ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
   SET NOCOUNT ON;
   IF 'SQL Login' =
     EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]',
                        'varchar(100)')
   BEGIN
      ROLLBACK;
      PRINT 'Creating a SQL login is not allowed.';
   END
   ELSE
   BEGIN
      PRINT 'Creating Windows NT Authenticated logins is allowed.';
END

END;
GO

--Test the trigger, should print error message
BEGIN TRY
   CREATE LOGIN DarshanSingh WITH PASSWORD = 'WjKkWjKf1234'
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE();
END CATCH
GO

--Test the trigger, should work fine
BEGIN TRY
   --Change the NT Login in the following statement
   CREATE LOGIN [DDGXPGuest] FROM WINDOWS;
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE();
END CATCH
GO

--Clean up
DROP TRIGGER trgTest2 ON ALL SERVER;
GO
--Change the NT Login in the following statement
DROP LOGIN [DDGXPGuest];
GO

This script creates a server-scoped DDL trigger to handle the CREATE_LOGIN event. The trigger code uses the EVENTDATA() function to get an xml type value that contains the event information. It then uses the value() XML method and an XQuery expression to see if the login being created uses SQL authentication. If it does, it rolls back the transaction and prints an error message. The rest of the statements in this batch test the trigger by first trying to create a SQL authenticated login, which should fail, and a Windows NT authenticated login, which should succeed. Remember to change the NT login name in the preceding script to match your machine or domain in place of DDGXP.

The ON DATABASE or ON ALL SERVER clause can be used with DROP TRIGGER to delete a DDL trigger, ALTER TRIGGER to modify a DDL trigger definition, DISABLE TRIGGER to disable a DDL trigger, and ENABLE TRIGGER to enable a DDL trigger. The CONTROL SERVER permission is required to create a DDL trigger at the server scope, and ALTER ANY TRIGGER permission is required to create a DDL trigger at the database scope.

For simplicity, SQL Server 2005 groups the related events into event groups on which you can define the triggers. For instance, instead of specifying FOR CREATE TABLE, ALTER TABLE, and DROP TABLE in the CREATE TRIGGER statement, you can just specify FOR DDL_TABLE_EVENTS, which includes the three table DDL statements.

In addition to having different purpose, DDL triggers differ from DML triggers in some ways. You cannot define an INSTEAD OF DDL trigger. The virtual inserted and deleted tables are not available and cannot be accessed inside the DDL trigger code; rather, the EVENTDATA() function is used to get the triggering event information, as illustrated previously.

Both DML triggers and DDL triggers are executed synchronously. This means that the action that leads to execution of a trigger waits until the trigger finishes executing. This is the reason DDL events such as CREATE DATABASE cannot be captured using DDL triggers: Such events are meant for asynchronous, non-transacted statements only.

SQL Server 2005 provides an alternative to DDL triggers—a mechanism called event notifications, which can be used to handle DDL events asynchronously. In addition to DDL events, event notification can also be used to handle profiler trace events, as discussed in the following section.

Event Notifications

The previous section illustrates how you can use DDL triggers to capture server and database events. DDL triggers have two limitations. First, the trigger code is executed synchronously and hence it does not yield a scalable solution. Second, it does not allow capturing some DDL events (such as CREATE DATABASE). In addition, let’s say you wanted to have the DDL trigger handler code executed on a different server. You could not do that by just using DDL triggers.

Event notifications allow you to handle database, server, and subsets of SQL trace events asynchronously. The CREATE EVENT NOTIFICATION T-SQL statement can be used to create an event notification object. You might wonder how the events are handled asynchronously. The answer involves Service Broker. With the CREATE EVENT NOTIFICATION statement, you provide the name of a Service Broker service to which the specified database, server, or SQL trace events will be posted. The events or messages posted to the Service Broker queue are then handled asynchronously. The event information is posted as XML to the specified Service Broker queue. Service Broker is a new technology introduced in SQL Server 2005 that brings the reliable and asynchronous messaging platform closer to the database engine. It is discussed in detail in Chapter 14, “SQL Server 2005 Service Broker.”

Here is a summary of how event notifications differs from DDL triggers:

• DDL triggers execute in the scope of the transaction of the action that causes them to fire and trigger code executes synchronously. If required, a DDL trigger can roll back the entire transaction. Event notifications, on the other hand, do not execute in the scope of the transaction of the action that causes them to fire, and hence they cannot roll back the transaction. Event notifications are processed asynchronously, using the Service Broker technology.

• In addition to database and server events, event notifications also support responding to a subset of SQL trace events.

• DDL triggers execute on the same server. Because event notifications are based on Service Broker, the events may be processed on a different server.

The following is the CREATE EVENT NOTIFICATION syntax:

CREATE EVENT NOTIFICATION event_notification_name
ON { SERVER | DATABASEQUEUE <queue_name>
[ WITH FAN_IN ]
FOR { event_type | event_group } [ ,...n ]
TO SERVICE <'broker_service'> ,
       {<'broker_instance_specifier'> | 'current database' } [;]

With the ON clause, you specify whether you want to handle database or server events or events on specified queue. With the FOR clause, you specify which events or event groups you want to listen on. With the TO SERVICE clause, you specify the name of the Service Broker service to which the event XML messages should be posted. The service and queue should already be created. If the Service Broker service uses the activation feature to execute a stored procedure when a message is posted in the queue, the stored procedure should also be already created, as you will see in the following example. If the WITH FAN_IN option is specified, SQL Server will group the same events and send them all together as a single event.

SET NOCOUNT ON
GO

USE [AdventureWorks];
GO

-- 1.Create the audit table
IF OBJECT_ID('dbo.tblAudit') IS NOT NULL
   DROP TABLE dbo.tblAudit;

GO
CREATE TABLE dbo.tblAudit
  (eventXMLData XML null);
GO

-- 2. Create the stored procedure
IF OBJECT_ID('dbo.sproc_Audit') IS NOT NULL
BEGIN
   DROP PROCEDURE dbo.sproc_Audit;
END
GO

CREATE PROCEDURE dbo.sproc_Audit
AS
BEGIN
   DECLARE @eventDataXML varbinary(max);
   RECEIVE TOP(1) @eventDataXML = message_body
    FROM [AdventureWorks].dbo.AuditQueue

   IF CAST(@eventDataXML as XML) IS NOT NULL
   BEGIN
      INSERT INTO dbo.tblAudit (eventXMLData)
         VALUES (CAST(@eventDataXML as XML));
   END
END
GO

-- 3. Create the service broker queue and service
IF EXISTS(SELECT * FROM sys.services WHERE name = 'AuditService')
    DROP SERVICE AuditService;
GO
IF OBJECT_ID('dbo.AuditQueue') IS NOT NULL AND
   EXISTS(SELECT * FROM sys.service_queues WHERE name = 'AuditQueue')
  DROP QUEUE dbo.AuditQueue;
GO

CREATE QUEUE dbo.AuditQueue
   -- Activation turned on
   WITH STATUS = ON,
   ACTIVATION (
      -- The name of the proc to process messages for this queue
      PROCEDURE_NAME = AdventureWorks.dbo.sproc_Audit,
      -- The maximum number of copies of the proc to start
      MAX_QUEUE_READERS = 1,
      -- Start the procedure as the user who created the queue.
      EXECUTE AS SELF )

   ON [DEFAULT] ;
GO

CREATE SERVICE
   AuditService ON QUEUE AuditQueue
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- 4. Setup event notification
CREATE EVENT NOTIFICATION ENDBAudit
ON SERVER
FOR CREATE_DATABASE, DROP_DATABASE
TO SERVICE 'AuditService', 'current database';
GO

-- 5. Try out the event notification
IF EXISTS (SELECT [name]
           FROM [master].[sys].[databases] WHERE [name] = N'TestDB1')
BEGIN
    DROP DATABASE TestDB1;
END
GO

CREATE DATABASE TestDB1;
GO

IF EXISTS (SELECT [name]
           FROM [master].[sys].[databases] WHERE [name] = N'TestDB1')
BEGIN
    DROP DATABASE TestDB1;
END
GO

--WAITFOR few seconds
WAITFOR DELAY '00:00:05';
GO

SELECT * FROM dbo.tblAudit;
GO

-- 6. Cleanup
DROP EVENT NOTIFICATION ENDBAudit ON SERVER ;
GO

IF OBJECT_ID('dbo.tblAudit') IS NOT NULL
   DROP TABLE dbo.tblAudit;

GO

IF EXISTS(SELECT * FROM sys.services WHERE name = 'AuditService')
    DROP SERVICE AuditService;
GO
IF OBJECT_ID('dbo.AuditQueue') IS NOT NULL AND
   EXISTS(SELECT * FROM sys.service_queues WHERE name = 'AuditQueue')
  DROP QUEUE dbo.AuditQueue;
GO

The script first creates an audit table and then a stored procedure that will be activated whenever an event message is posted to the Service Broker queue. This stored procedure retrieves the message from the queue, casts it as XML, and inserts a row into the audit table. Next, a Service Broker queue and service is created. The CREATE EVENT NOTIFICATION statement then uses this service to monitor CREATE_DATABASE and DROP_DATABASE server events. To try out the event notification mechanism, the script then creates and drops a test database. The SELECT statement on the audit table should show the two rows, containing the event data as XML documents. The last step in the script does the clean-up and removes the objects created. When you run this script, you see two records in the audit table, containing the following XML document:

<EVENT_INSTANCE>
  <EventType>CREATE_DATABASE</EventType>
  <PostTime>2005-06-26T17:44:38.077</PostTime>
  <SPID>53</SPID>
  <ServerName>DDGXPJUNECTP</ServerName>
  <LoginName>DDGXPDarshan Singh</LoginName>
  <DatabaseName>TestDB1</DatabaseName>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE DATABASE TestDB1;</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

<EVENT_INSTANCE>
  <EventType>DROP_DATABASE</EventType>
  <PostTime>2005-06-26T17:44:38.127</PostTime>
  <SPID>53</SPID>
  <ServerName>DDGXPJUNECTP</ServerName>
  <LoginName>DDGXPDarshan Singh</LoginName>
  <DatabaseName>TestDB1</DatabaseName>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>DROP DATABASE TestDB1;</CommandText>

  </TSQLCommand>
</EVENT_INSTANCE>

You can view the metadata for database, object, or assembly event notifications by using the sys.event_notifications catalog view; and you can view the metadata for server event notifications by using the sys.server_event_notifications catalog view. Some other catalog views related to event notifications include sys.event_notification_event_types, sys.trace_events, sys.events, and sys.server_events.

Snapshot Isolation

SQL Server 2005 introduces a new isolation level called snapshot isolation, which, like REPEATABLE READ, avoids dirty reads and non-repeatable reads, but unlike with REPEATABLE READ, with snapshot isolation, readers don’t block writers. Snapshot isolation is based on a technique called as row versioning, which makes use of the tempdb system database to keep a version of updated rows. With snapshot isolation, if a transaction updates or deletes a row that is already read by another transaction, SQL Server copies the original version of the row to the tempdb database. If the transaction tries to read the row again, instead of reading the row from the user database, SQL Server reads the row from the tempdb database, and hence avoids the non-repeatable read issue; at the same time, it avoids the blocking. This increases the concurrency and data availability and reduces the locking and deadlocking.

Snapshot isolation in SQL Server 2005 is exposed in two ways:

• By turning on the READ_COMMITTED_SNAPSHOT database option. Then the default READ COMMITTED isolation level makes use of row versioning instead of locks to protect transactions from dirty reads and to support repeatable reads. Once the READ_COMMITTED_SNAPSHOT database option is turned on, all the READ COMMITTED transactions in that database use row versioning to run in snapshot isolation mode.

• By turning on the ALLOW_SNAPSHOT_ISOLATION database option. Then you set the session’s isolation level to snapshot isolation by running the SET TRANSACTION ISOLATION LEVEL SNAPSHOT T-SQL statement. With this, only the transactions in the current session are run in snapshot isolation mode.

Snapshot isolation is discussed in great detail, including its pros and cons and examples, in Chapter 9, “Performance Analysis and Tuning.”

Ranking Functions

SQL Server 2005 introduces a new category of built-in functions: analytical ranking functions. This category consists of four functions—ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()—that you can use to generate a sequence number or a sequential number for each row or to rank rows based on some partition criteria. The main idea behind providing these functions is to reduce the complexity and amount of query code that you have to write in order to efficiently calculate row numbers or rank the rows. Let’s look at an example of this and then examine each function in detail.

The goal of this example is to output a column containing a sequential number based on a unique combination of ProductID and LocationID columns in the Production.ProductInventory table in the AdventureWorks sample database. Here is how you would do it without using any ranking function:

USE AdventureWorks;
GO
SELECT
  ( SELECT COUNT(*)
    FROM Production.ProductInventory AS pi2
    WHERE
         pi2.ProductID < pi1.ProductID OR
         (
          pi2.ProductID = pi1.ProductID AND
          pi2.LocationID <= pi1.LocationID
         )
  ) AS SequenceNo, *
FROM Production.ProductInventory AS pi1
ORDER BY ProductID, LocationID;
GO

This query produces the following output (some columns and rows have been omitted for brevity):

SequenceNo  ProductID   LocationID Shelf      Bin  Quantity
----------- ----------- ---------- ---------- ---- --------
1           1           1          A          1    408
2           1           6          B          5    324
3           1           50         A          5    353
4           2           1          A          2    427
5           2           6          B          1    318
6           2           50         A          6    364

Here is how you can obtain the same results by using the new ROW_NUMBER() ranking function:

SELECT ROW_NUMBER() OVER (ORDER BY ProductID, LocationID) as SequenceNo, *
FROM Production.ProductInventory;
GO

Let’s assume that you want to reset the sequence number and start over from number 1 when the ProductID changes. Here is how you would do it without using any ranking function:

SELECT
  ( SELECT COUNT(*)
    FROM Production.ProductInventory AS pi2
    WHERE pi2.ProductID = pi1.ProductID AND
         (pi2.ProductID < pi1.ProductID OR
           (pi2.ProductID = pi1.ProductID AND
            pi2.LocationID <= pi1.LocationID)
         )
  ) AS SequenceNo, *
FROM Production.ProductInventory AS pi1
ORDER BY ProductID, LocationID;
GO

This query produces the following output (some columns and rows have been omitted for brevity):

SequenceNo  ProductID   LocationID Shelf      Bin  Quantity
----------- ----------- ---------- ---------- ---- --------
1           1           1          A          1    408
2           1           6          B          5    324
3           1           50         A          5    353
1           2           1          A          2    427
2           2           6          B          1    318
3           2           50         A          6    364
1           3           1          A          7    585
2           3           6          B          9    443

You can obtain the same results by using the new ROW_NUMBER() ranking function as shown here:

SELECT ROW_NUMBER() OVER
(PARTITION BY ProductID ORDER BY ProductID, LocationID) as SequenceNo, *
FROM Production.ProductInventory;
GO

As you can see in these two examples, you write less code when you use the ranking functions, the queries are simple to understand and maintain. And if you study the execution plan for these queries, you notice that ranking functions perform better than the traditional approach.

The ROW_NUMBER Ranking Function

As illustrated in the preceding section, the ROW_NUMBER ranking function can be used to sequentially number starting at 1 for the first row. You can partition the result set and restart the sequential numbering by using the PARTITION BY clause, also illustrated in the preceding section.

One common application where ROW_NUMBER can be very valuable is when you are building a web application and need to implement the paging in the result set:

SELECT * FROM
   (SELECT ROW_NUMBER() OVER (ORDER BY ProductID, LocationID) as SequenceNo, *
    FROM Production.ProductInventory) AS tblRows
WHERE SequenceNo BETWEEN 101 and 120;
GO

Note that you can directly use the alias for the ROW_NUMBER function; you simply have to nest the query as shown here to use the ROW_NUMBER column alias (SequenceNo in this case) in the WHERE condition.

The RANK and DENSE_RANK Ranking Functions

The RANK and DENSE_RANK ranking functions are similar to the ROW_NUMBER function as they also produce ranking values according to sorting criteria, optionally partitioned into groups of rows. However, unlike the ROW_NUMBER function, which generates a different value for each row, the RANK and DENSE_RANK functions output the same value for all rows that have the same values in the sort column. The RANK and DENSE_RANK functions differ in the way the rank number value is generated when the sort order column value changes. The following example illustrates this:

SELECT RANK() OVER (ORDER BY ProductID) as Rank,
       DENSE_RANK() OVER (ORDER BY ProductID) as DenseRank,
       *
FROM Production.ProductInventory;

This query produces the following output (some columns and rows have been omitted for brevity):

Rank   DenseRank  ProductID   LocationID Shelf  Bin  Quantity
------ ---------- ----------- ---------- ------ ---- --------
1      1          1           1          A      1    408
1      1          1           6          B      5    324
1      1          1           50         A      5    353
4      2          2           1          A      2    427
4      2          2           6          B      1    318
4      2          2           50         A      6    364
7      3          3           1          A      7    585
7      3          3           6          B      9    443
7      3          3           50         A      10   324
10     4          4           1          A      6    512

When ProductID changes, the DENSE_RANK function increments the rank value by 1. However, the RANK function increments the rank value by the number of rows for the preceding rank.

The NTILE Ranking Function

The fourth and final ranking function introduced in SQL Server 2005, NTILE can be used to assemble rows into a desired number of buckets according to specified sorting criteria, optionally within partitions. This function takes an integer value that indicates how many groups you want:

SELECT NTILE(3) OVER (ORDER BY ShipMethodID) AS Bucket, *
FROM Purchasing.ShipMethod;

The Purchasing.ShipMethod table contains five rows. NTILE(3) divides those five rows into three buckets, returning values 1 through 3 in the first column, named Bucket, as the output of preceding query (some columns have been omitted for brevity):

Bucket   ShipMethodID Name
-------  ------------ --------------------
1        1            XRQ - TRUCK GROUND
1        2            ZY - EXPRESS
2        3            OVERSEAS - DELUXE
2        4            OVERNIGHT J-FAST
3        5            CARGO TRANSPORT 5

Note how the NTILE function handles the situation when even distribution of rows is not possible. It puts two rows into the first group, two rows into the second group, and the remaining one row into the third group. When even distribution is not possible, larger groups precede smaller groups. Implementing this functionality in SQL Server 2000 is possible but a little complex, and it requires more code.

New Relational Operators: PIVOT, UNPIVOT, and APPLY

If you have ever worked with Microsoft Access, you might have used the TRANSFORM statement to create a crosstab query. Similar functionality is provided by SQL Server 2005 via the PIVOT operator with the SELECT statement. The PIVOT operator can be used to transform a set of rows into columns. The UNPIVOT operator complements the PIVOT operator by allowing you to turn columns into rows.

The following simple example illustrates the use of the PIVOT and UNPIVOT operators:

USE AdventureWorks;
GO
IF OBJECT_ID('dbo.tblCars') IS NOT NULL
   DROP TABLE dbo.tblCars;
GO

CREATE TABLE dbo.tblCars
   (RecordID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
   DealerID INT NOT NULL,
   Make NVARCHAR(50),
   MakeYear SMALLINT,
   CarsSold INT);
GO

INSERT INTO dbo.tblCars SELECT 1, 'Honda', 2003, 100;
INSERT INTO dbo.tblCars SELECT 2, 'Toyota', 2003, 500;
INSERT INTO dbo.tblCars SELECT 2, 'Honda', 2003, 200;
INSERT INTO dbo.tblCars SELECT 1, 'Honda', 2004, 200;
INSERT INTO dbo.tblCars SELECT 1, 'Toyota', 2004, 600;
INSERT INTO dbo.tblCars SELECT 2, 'Honda', 2004, 300;
INSERT INTO dbo.tblCars SELECT 2, 'Toyota', 2005, 50;
GO
SELECT * FROM dbo.tblCars;
GO

SELECT Make, [2003], [2004], [2005] FROM
   (
      SELECT Make, CarsSold, MakeYear FROM dbo.tblCars
   ) tblCars
   PIVOT (SUM(CarsSold) FOR MakeYear IN ([2003],[2004], [2005])) tblPivot;
GO

This script creates a sample table to store car sales data. It then inserts some sample data into that table, as shown here:

RecordID    DealerID    Make    MakeYear CarsSold
----------- ----------- ------- -------- ---------
1           1           Honda   2003     100
2           2           Toyota  2003     500
3           2           Honda   2003     200
4           1           Honda   2004     200
5           1           Toyota  2004     600
6           2           Honda   2004     300
7           2           Toyota  2005     50

The PIVOT statement transforms rows into columns, at the same time calculating the total sales per year. It produces the following results:

Make    2003   2004   2005
------- ------ ------ ------
Honda   300    500    NULL
Toyota  500    600    50

Here’s how you save this pivoted data into a table and then use UNPIVOT to transform columns into rows:

IF OBJECT_ID('dbo.tblPivotData') IS NOT NULL
   DROP TABLE dbo.tblPivotData;
GO

SELECT Make, [2003], [2004], [2005] INTO dbo.tblPivotData FROM
   (
      SELECT Make, CarsSold, MakeYear FROM dbo.tblCars
   ) tblCars
   PIVOT (SUM(CarsSold) FOR MakeYear IN ([2003],[2004], [2005])) tblPivot;
GO

SELECT Make, tblUnPivot.MakeYear, tblUnPivot.CarsSold
FROM dbo.tblPivotData
     UNPIVOT
     (CarsSold for MakeYear in ([2003],[2004], [2005])) tblUnPivot;
GO

Using the UNPIVOT clause results in the following output:

Make    MakeYear   CarsSold
------- ---------- -----------
Honda   2003       300
Honda   2004       500
Toyota  2003       500
Toyota  2004       600
Toyota  2005       50

Refer to the pivoted data shown earlier (with 2003, 2004, and 2005 as the columns). All UNPIVOT does is convert columns into rows.

The two common applications of the PIVOT operator are to create an analytical view of the data, as illustrated previously, and to implement an open schema, as illustrated here:

IF OBJECT_ID('dbo.tblServers') IS NOT NULL
   DROP TABLE dbo.tblServers;
GO

CREATE TABLE dbo.tblServers(
ServerID INT IDENTITY(1,1),
ServerName VARCHAR(50));
GO

INSERT INTO dbo.tblServers SELECT 'Server_1';
INSERT INTO dbo.tblServers SELECT 'Server_2';

INSERT INTO dbo.tblServers SELECT 'Server_3';
GO

IF OBJECT_ID('dbo.tblServerEquip') IS NOT NULL
   DROP TABLE dbo.tblServerEquip;
GO

CREATE TABLE dbo.tblServerEquip(
   ServerID INT,
   EquipmentType VARCHAR(30),
   Description VARCHAR(100));
GO

INSERT INTO dbo.tblServerEquip SELECT 1,'Hard Drive','40GB';
INSERT INTO dbo.tblServerEquip SELECT 1,'Memory Stick','512MB';
INSERT INTO dbo.tblServerEquip SELECT 2,'Memory Stick','512MB';
INSERT INTO dbo.tblServerEquip SELECT 2,'Hard Drive','40GB';
INSERT INTO dbo.tblServerEquip SELECT 2,'NIC','10 MBPS';
INSERT INTO dbo.tblServerEquip SELECT 3,'Memory Stick','512MB';
INSERT INTO dbo.tblServerEquip SELECT 3,'Hard Drive','40GB';
INSERT INTO dbo.tblServerEquip SELECT 1,'Hard Drive','100GB';
INSERT INTO dbo.tblServerEquip SELECT 1,'NIC','10 MBPS';I
INSERT INTO dbo.tblServerEquip SELECT 1,'NIC','1GB Fibre Channel';
GO

SELECT * FROM
   ( SELECT s.ServerName, se.EquipmentType, se.Description
     FROM dbo.tblServers s
     INNER JOIN dbo.tblServerEquip se
     ON s.ServerID = se.ServerID
   ) AS pnt
   PIVOT
   (
      COUNT(Description)
      FOR EquipmentType IN([Hard Drive], [Memory Stick], [NIC])
   ) AS pvt;
GO

Using the PIVOT command produces the following output:

ServerName    Hard Drive  Memory Stick NIC
------------- ----------- ------------ -----------
Server_1      2           1            2
Server_2      1           1            1
Server_3      1           1            0

This script creates a table to store all the servers in a production environment and another table to store server equipment type. The PIVOT operator is used to join these two tables to create a simplified view that shows the server name and counts of the equipment in that server.

The third new operator, APPLY, can be used to invoke a table-valued function for each row in the rowset. A table-valued function is a function that returns a rowset (that is, a table) as a return value.

For each row in the outer query in which the APPLY operator is used, the table-value function is called, and the columns returned by the function are appended to the right of the columns in the outer query, to produce a combined final output. You can pass the columns from the outer query as the parameters to the table-valued function specified with the APPLY operator. Here is an example of using the APPLY operator:

IF OBJECT_ID('dbo.GetEmpHierarchy') IS NOT NULL
   DROP FUNCTION dbo.GetEmpHierarchy;
GO

CREATE FUNCTION dbo.GetEmpHierarchy (@EmployeeID AS INT) RETURNS TABLE
AS
   RETURN
      WITH RecCTE(ManagerID, EmployeeID, EmployeeLevel) AS
      (
          SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
             FROM HumanResources.Employee
             WHERE EmployeeID = @EmployeeID

          UNION ALL

          SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
             FROM HumanResources.Employee e
             INNER JOIN RecCTE r
             ON e.ManagerID = r.EmployeeID
      )
      SELECT EmployeeID, ManagerID, EmployeeLevel
      FROM RecCTE;
GO

SELECT s.SalesPersonID, s.SalesOrderID, s.CustomerID, s.TotalDue, tvf.*
FROM Sales.SalesOrderHeader AS s
     CROSS APPLY dbo.GetEmpHierarchy(s.SalesPersonID) AS tvf
ORDER BY TotalDue DESC;
GO

This script defines a table-valued function that accepts EmployeeID as an input parameter and returns a table by using a recursive CTE (as discussed earlier in this chapter, in the section “Common Table Expressions (CTEs)”) to return the hierarchy for that employee. The APPLY operator is used in the SELECT statement so that for each row in the Sales.SalesOrderHeader table, the query returns the employees directly or indirectly reporting to the current SalesPersonID. If you run the script in the AdventureWorks sample database and scroll down in the result set grid, you see some Sales.SalesOrderHeader rows repeated with the employee hierarchy shown, using the three columns on the right side.

APPLY can take two forms: CROSS APPLY and OUTER APPLY. With CROSS APPLY, if the tablevalued function returns an empty set for a given outer row, that outer row is not returned in the result. On the other hand, OUTER APPLY also returns rows from the outer table for which the table-valued function returned an empty set. As with OUTER JOINs, NULL values are returned as the column values that correspond to the columns of the table-valued function.

The OUTPUT Clause

SQL Server 2005 introduces the OUTPUT clause to capture the data rows changed when a DML statement—that is, INSERT, UPDATE, or DELETE—is executed. The OUTPUT clause can be specified with any of the DML operations; you can use the inserted and deleted virtual tables with the OUTPUT statement and copy the affected rows into a table variable.

Here is an example of using the OUTPUT clause to capture the rows updated:

USE AdventureWorks;
GO
BEGIN TRANSACTION;
DECLARE @varOriginalRows AS TABLE
(
    CustomerID INT,
    Name NVARCHAR(50),
    SalesPersonID INT,
    Demographics XML,
    rowguid uniqueidentifier,
    ModifiedDate datetime
);

UPDATE Sales.Store
SET SalesPersonID = 280
OUTPUT deleted.* INTO @varOriginalRows
WHERE SalesPersonID = 282;

SELECT * FROM @varOriginalRows;

ROLLBACK TRANSACTION;
GO

The preceding script declares a table variable that has the same schema as Sales.Store because you want to capture all the columns with the OUTPUT clause. The UPDATE DML statement uses the OUTPUT clause to capture all the rows that are affected and saves them into the @varOriginalRows table variable. The script is executed in a transaction scope, which is rolled back toward the end to avoid making any changes to the table.

The BULK Rowset Provider

SQL Server 2005 enhances the OPENROWSET function by providing a BULK rowset provider that can be used to read a file and return the file contents as a rowset, which can then be bulk loaded into a table. You can optionally specify an XML or non-XML format file, using the same format file structure as the bcp.exe utility.

The SINGLE_BLOB option tells the BULK rowset provider to return the contents of a file as a single-row, single-column rowset of type varbinary(max). With the SINGLE_CLOB option, the BULK rowset provider returns the contents of the file as a single-row, single-column rowset of type varchar(max), and with SINGLE_NCLOB it returns a single-row, singlecolumn rowset of type nvarchar(max). SINGLE_NCLOB requires that the input file to be saved in Unicode (widechar) format.

Let’s look at an example of BULK rowset provider. Let’s assume that the following text is available in a text file called c:property.txt:

This charming almost new home offers glistening hardwood floors and a formal
living and dining room. The Great Room is two story and is basked in sunshine
and opens onto the breakfast room and huge kitchen. Upstairs, an outstanding
master suite boasts a luxury bath w/ separate shower! Nestled into a
cul de sac high on a knoll, this wonderful home is available for immediate
occupancy.

Here is how you would bulk load this text into a table, using the OPENROWSET function and the BULK rowset provider:

USE AdventureWorks;
GO
IF OBJECT_ID('dbo.tblProperty') IS NOT NULL
   DROP TABLE dbo.tblProperty;
GO
CREATE TABLE dbo.tblProperty
   (MLSID INT PRIMARY KEY NOT NULL,
    Description VARCHAR(max));
GO

INSERT INTO dbo.tblProperty
      SELECT 12345, txt.*
      FROM OPENROWSET(BULK N'C:Property.txt',
               SINGLE_CLOB) AS txt;

SELECT * FROM dbo.tblProperty;
GO

If you run the preceding script, you should notice that data from the text file is inserted into the Description column.

New Declarative Referential Integrity Actions

The two declarative referential integrity (DRI) actions available in SQL Server 2000 are NO ACTION and CASCADE. When you create or alter a table, you can define the DRI actions on foreign key columns to tell SQL Server what to do when the referenced row is deleted from the parent table. For example, when defining the ContactDetails table, you can specify CASCADE to delete the rows when the referred row is deleted from the Customer table, or you can specify NO ACTION, in which case SQL Server raises an error indicating that you cannot delete the rows from Customer table because they are being referenced in the ContactDetails table.

SQL Server 2005 introduces two new DRI actions: SET NULL and SET DEFAULT. When SET NULL is specified, first you have to make sure that the foreign key column is nullable, and then, when rows from the parent table are deleted, SQL Server puts NULL in the referenced foreign key column. When SET DEFAULT is specified, first you have to make sure that the column is either nullable or has a default definition, and then when rows from the parent table are deleted, SQL Server puts the default value in the referenced foreign key column. If a default constraint is not defined on the column and the column is nullable, SQL Server puts NULL in that column.

In addition to using the delete operation, you can also specify SET NULL and SET DEFAULT actions for the instances when the referenced row is updated in the parent table.

Here is an example of the new SET NULL and SET DEFAULT actions:

CREATE TABLE dbo.tblMaster
   (CustomerID INT PRIMARY KEY NOT NULL,
    Name VARCHAR(100));
GO

CREATE TABLE dbo.tblChild1
   (AddressID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    CustomerID INT NULL FOREIGN KEY REFERENCES dbo.tblMaster (CustomerID)
    ON DELETE SET NULL,
    AddressLine1 VARCHAR(100));
GO

CREATE TABLE dbo.tblChild2
   (AddressID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    CustomerID INT NOT NULL DEFAULT -1
    FOREIGN KEY REFERENCES dbo.tblMaster (CustomerID)
    ON DELETE SET DEFAULT,
    AddressLine1 VARCHAR(100));
GO

INSERT INTO dbo.tblMaster SELECT 1, 'Customer1';
INSERT INTO dbo.tblMaster SELECT 2, 'Customer2';
INSERT INTO dbo.tblMaster SELECT 3, 'Customer3';
INSERT INTO dbo.tblMaster SELECT −1, 'Invalid Customer';

INSERT INTO dbo.tblChild1 SELECT 1, 'Customer1Address';
INSERT INTO dbo.tblChild1 SELECT 2, 'Customer2Address';
INSERT INTO dbo.tblChild1 SELECT 3, 'Customer3Address';

INSERT INTO dbo.tblChild2 SELECT 1, 'Customer1Address';
INSERT INTO dbo.tblChild2 SELECT 2, 'Customer2Address';
INSERT INTO dbo.tblChild2 SELECT 3, 'Customer3Address';

DELETE dbo.tblMaster WHERE CustomerID = 2;
GO

SELECT * FROM dbo.tblChild1;
SELECT * FROM dbo.tblChild2;
GO

This script creates a table that is referenced by two other tables. For the first referenced table, tblChild1, you specify the DRI action to set the foreign key column to NULL if the row is deleted from the parent table. For the second referenced table, tblChild2, you specify the DRI action to set the foreign key column to a default value of -1 if the row is deleted from the parent table. In the case of the default value, the value must be present in the parent table (Customer, with -1 as the CustomerID in this case). When the customer with CustomerID as 2 is deleted, the corresponding CustomerID foreign key column in tblChild1 is set to NULL, and the corresponding CustomerID foreign key in the tblChidl2 table is set to the default value of -1.

Metadata Views and Dynamic Management Views

With SQL Server 2000, T-SQL developers often used system tables, system stored procedures, and SQL-99 INFORMATION_SCHEMA views to access system metadata to get answers about things such as numbers and names of tables in a database; find out about constraints, indexes, and keys defined for a table; determine the number of columns in a table or the number and names of databases on a server; and so on.

The SQL-99 INFORMATION_SCHEMA views are still available in SQL Server 2005, but system tables are no longer directly accessible. SQL Server 2005 introduces the concept of catalog views, which is a recommended way to access the system metadata. The catalog views provide a consistent and secure interface to access system metadata in SQL Server 2005. All the catalog views are defined in the sys schema. For backward compatibility with previous releases, SQL Server 2005 also provides the compatibility views, which have the same names and return the same data as the system tables. However, using the compatibility views is discouraged, and accessing catalog views is the recommended option if you need to access the system metadata. The INFORMATION_SCHEMA views are not updated to cover all the new features, such as Service Broker, introduced in SQL Server 2005. The catalog views are the only metadata-access interface that covers all the new SQL Server 2005 features. The catalog views are also more efficient than compatibility views and INFORMATION_SCHEMA views.

Table 6.1 lists the catalog view categories and examples of catalog views in each category.

Table 6.1. SQL Server 2005 Catalog Views

Dynamic management views (DMVs) are one more type of view provided with SQL Server 2005. DMVs can be used to view the current state of the SQL Server 2005 system. In other words, DMVs provide a real-time snapshot of internal memory structures that indicate the server state. This means that instead of using sp_who, you can now use the sys.dm_exec_sessions DMV to effectively get more current details on users logged in. Like catalog views, DMVs are also defined in the sys schema. All DMV names begin with dm_, and the next word in the name indicates the DMV category (for example, sys.dm_exec_sessions refers to the execution category). SQL Server 2005 contains several DMVs to get current memory, I/O, an index, a full-text index, Service Broker information, and transaction activity. DMVs are discussed in more detail in Chapter 9.

Miscellaneous T-SQL Enhancements

Before concluding this chapter, let’s look at some other enhancements made to T-SQL in SQL Server 2005 release.

The ALTER INDEX Statement

The ALTER INDEX statement, which is new in SQL Server 2005, can be used to disable, rebuild, or reorganize the XML and relational indexes. This statement can also be used to set the index options, such as whether row locks or page locks are allowed when accessing the index, whether index statistics should be automatically recomputed, and so on.

Here is an example of using ALTER INDEX to disable and enable an index:

USE AdventureWorks;
GO

ALTER INDEX AK_SalesOrderHeader_rowguid ON Sales.SalesOrderHeader
DISABLE;
GO

SELECT is_disabled, * FROM sys.indexes
WHERE name = 'AK_SalesOrderHeader_rowguid';
GO

ALTER INDEX AK_SalesOrderHeader_rowguid ON Sales.SalesOrderHeader
REBUILD;
GO

SELECT is_disabled, * FROM sys.indexes
WHERE name = 'AK_SalesOrderHeader_rowguid';
GO

Statement-Level Recompilation

In SQL Server 2000, when schema changes were made to the objects referenced in a stored procedure, when SET options were changed, or when statistics were changed, SQL Server would recompile the entire stored procedure. SQL Server 2005 introduces a new optimization technique in which only the statement that caused the recompilation, instead of entire stored procedure, is compiled. This results in faster recompile times, fewer compile locks, less CPU and memory utilization, and overall better performance in a T-SQL module. In addition to this built-in enhancement, a new query hint, OPTIMIZE FOR, is provided that you can use to instruct SQL Server to use a particular value for a local variable when the query is compiled and optimized.

New Server Configuration Options

The security-related changes in SQL Server 2005 might break your existing T-SQL scripts. For instance, by default, the xp_cmdshell extended stored procedure and SQL Mail stored procedures are disabled in SQL Server 2005. An error is raised when xp_cmdshell or SQL Mail procedures are executed. You can use the new security tool, SQL Server Surface Area Configuration, or the sp_configure statement to enable or disable features such as the xp_cmdshell extended stored procedure, SQL Mail procedures, Web Assistant stored procedures, and OLE Automation extended stored procedures. To view these options using sp_configure, you have to first enable the show advanced options server configuration option.

Synonyms

SQL Server 2005 permits creating synonyms that serve as alternative names for another database object, referred to as the base object. The base object can exist on a local or remote server. Synonyms can also be used to create a layer of abstraction that protects the client application from changes made to the name or location of the base object.

You can create a synonym by using the CREATE SYNONYM DDL statement, and you can remove it by using the DROP SYNONYM statement, as illustrated in the following example:

USE AdventureWorks;
GO
IF OBJECT_ID('edh') IS NOT NULL
   DROP SYNONYM edh;
GO
CREATE SYNONYM edh FOR HumanResources.EmployeeDepartmentHistory;
GO
SELECT * FROM edh;

GO
SELECT * FROM sys.synonyms;
GO
IF OBJECT_ID('edh') IS NOT NULL
   DROP SYNONYM edh;
GO

You can use the sys.synonyms catalog view to view the metadata, including the base table name, for the synonym. When you run the CREATE SYNONYM statement, it is not required for the base object to exist at that time, and no permissions on the base object are checked. When the synonym is accessed, the base object existence and permission check is done. If base object and synonym owners are the same, SQL Server just ensures that the user accessing the synonym has sufficient permissions on the synonym. If the synonym and base object owners are different, SQL Server ensures that the user accessing the synonym has sufficient permissions on both the synonym and the base object.

The EXCEPT and INTERSECT Operators

The EXCEPT and INTERSECT operators allow you to compare the results of two or more SELECT statements and return distinct values. The EXCEPT operator returns any distinct values from the query on the left side of the EXCEPT operator that are not returned by the query on the right side. INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

Result sets that are compared using EXCEPT or INTERSECT must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types.

Here is an example of the EXCEPT and INTERSECT operators:

USE [tempdb];
GO

IF OBJECT_ID('dbo.t1') IS NOT NULL
   DROP TABLE dbo.t1;
GO
IF OBJECT_ID('dbo.t2') IS NOT NULL
   DROP TABLE dbo.t2;
GO

CREATE TABLE dbo.t1(col1 int, col2 int);
GO
CREATE TABLE dbo.t2(col1 int, col2 int);
GO

INSERT INTO dbo.t1 SELECT 1, 1;
INSERT INTO dbo.t1 SELECT 2, 2;
INSERT INTO dbo.t1 SELECT 3, 3;

INSERT INTO dbo.t2 SELECT 1, 1;
INSERT INTO dbo.t2 SELECT 2, 2;
GO

SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2;
GO

SELECT * FROM dbo.t1 INTERSECT SELECT * FROM dbo.t2;
GO

The first SELECT statement in this script returns a row with col1 and col2 as 3 because this is the row that is present in dbo.t1 and not in the dbo.t2 table. The second SELECT statement returns the first two rows because they are present in both the tables.

The SET SHOWPLAN_XML and SET STATISTICS XML Statements

SQL Server 2005 provides two new SET statements—SET SHOWPLAN_XML and SET STATISTICS XML—that can be used to obtain a query showplan and statistics as an XML document. These XML results can then be processed in many ways, and they also open up the opportunity for third-party application vendors to provide add-on tools to optimize and compare performance results. Here is an example of these two statements:

USE AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Sales.Store;
GO
SET SHOWPLAN_XML OFF;
GO
SET STATISTICS XML ON;
SELECT * FROM Sales.Store;
SET STATISTICS XML OFF;
GO

If you run the queries, you can see how the showplan and query statistics are returned as XML values. You can run the preceding script in Management Studio and view the results in grid mode. The showplan XML appears as a hyperlink. You can click the showplan XML hyperlink to view the XML text in a new window. You can copy and paste the showplan XML text into a text file that has the extension .sqlplan. Then you can start Windows Explorer and double-click the .sqlplan file or select File, Open (Ctrl+O) from Management Studio and open the .sqlplan file that you just created. You should notice that Management Studio reads the XML showplan and shows the execution plan graphically.

Summary

This chapter highlights the new and improved T-SQL language features in SQL Server 2005. It begins with a comparison of T-SQL with SQLCLR to help you decide where to use which option. T-SQL is still the primary language that DBAs and developers should use for any kind of data access and scripting.

The rest of the chapter discusses the T-SQL enhancements in SQL Server 2005. SQL Server 2005 has enhanced the TOP operator and introduces a new TABLESAMPLE keyword that you can use to restrict the result set. The chapter next talks about a very cool T-SQL feature, CTE, which can be used instead of derived tables to produce simpler and efficient queries. The real power of CTEs can be seen when they are used recursively.

The other features discussed in this chapter include the max specifier to store 2GB of character or binary data in varchar, nvarchar, or varbinary columns; structural error handling using TRY...CATCH blocks; DDL triggers to handle database- and server-level DDL events; event notifications to asynchronously handled database, server, and SQL trace events; the new snapshot isolation level to reduce locking and increase concurrency and scalability; analytical ranking functions; new relational operators, including PIVOT, UNPIVOT, and APPLY; the OUTPUT clause to capture changes made by DML statements; enhancements to the OPENROWSET function to bulk load data; and metadata views, index enhancements, and the XML showplan.

Chapter 7, “SQL Server 2005 Security,” covers the security-related features in SQL Server 2005.