Top 10 SQL Server 2005 New Features for Developers

By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

May 6, 2006

You may have heard lots of hype about SQL Server 2005 new features. Way and beyond the stable Transact-SQL which hardly changed anything in the last 15 years. You have even seen scripts and demonstrations of the new feature, and now you are convinced; SQL 2005 is much better than SQL 2000. But is it really? Based on my 15 years of experience with T-SQL in heavy-duty practical applications of Wall Street high finance and consumer websites, I came up with a rating. These features are mainly for programming, not administration or operations. Albeit sometimes the lines are blurred: a new programming feature may turn out to be an operational godsend as well. So without much further ado, here comes the Top 10 list for Microsoft's latest version of SQL Server.

1. TRY/CATCH Error Control

Without doubt this is the most beneficial new feature of SQL 2005. It introduces soft error control into T-SQL, the same kind available in standard programming languages. Well, a simplified version, but it does the job miraculously. Prior to SQL 2005 if a script encountered an error, that was the end. An application can deal with it, by catching the error, but an SQL agent job written in T-SQL cannot. Typical example: you are moving million rows from a staging table to final destination. The staging table date is in varchar format, the destination column is datetime format. The conversion fails on “2006 janu 44”. Which is row 804249. But you only know that after ½ hour of intense research. SQL 2000 T-SQL gives no indication where it fails. It just bombs. It may have taken 10 minutes to reach the point of failure; it may take another 10 minutes to roll back the 804248 good insert transactions; at the end you ended up with nothing. In SQL 2005 T-SQL, instead of bombing, control is passed to the CATCH routine that for example can log the erroneous data, and upon finishing with it, the control flow is returned to a designated T-SQL statement. If this processing is in a loop, the loop would continue. Here is an example. If you change 1/0 to 1/1, it will not go to the CATCH processing; the "try" succeeds.

PRINT 'Start of processing'
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT Infinity = 1/0;
PRINT 'No error'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
PRINT 'Error occured'
END CATCH;
PRINT 'Processing continues'
GO

In SQL 2000 and prior T-SQL, the divide by zero would terminate all processing, open transactions would be rolled back, and no opportunity would be given to recover from the error and continue processing. The error is raised though which can be processed by an application.

With TRY/CATCH, errors can be taken care of on the server side without involving the client side at all. That is a huge productivity gain; also it leads to a more reliable operational environment.

2. CTE – Common Table Expression

CTE is a neat way of writing complex queries without the use of temp tables or derived tables. CTE itself can be considered a temp table with scope limited to the query. Unlike derived tables which are intermingled with the main query, CTE-s are placed neatly to the top part of the query, greatly simplifying the main query itself. CTE-s will increase developer’s productivity by introducing “structured programming” into complex queries. Here is an example for a double CTE query where the second CTE is used to resolve both the employee name and his/her manager's name.

WITH cteEmployeeOrders(EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
cteEmployeeName(Employee, Employeeid)
AS
(
SELECT LastName+', '+ FirstName, Employeeid
FROM HumanResources.Employee E
JOIN Person.Contact C
ON E.ContactID = C.ContactID
)
SELECT Employee = EN1.Employee, OrdersTaken=OE.NumOrders,
LastOrderDate=OE.MaxDate,
Manager = EN2.Employee, OrdersTaken=OM.NumOrders,
LastOrderDate = OM.MaxDate
FROM HumanResources.Employee AS E
JOIN cteEmployeeOrders AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN cteEmployeeOrders AS OM
ON E.ManagerID = OM.EmployeeID
JOIN cteEmployeeName EN1
ON E.EmployeeID = EN1.EmployeeID
JOIN cteEmployeeName EN2
ON E.ManagerID = EN2.EmployeeID

3. Column Headers to Clipboard When Copying Results

How many times you had to produce an ad-hoc report and copy the results window into an Excel spreadsheet? Except the column headers did not come. If the report was destined to high circles, you had to manually type in 20 column headers?

With SQL 2005 you can turn on the option to include column headers when copying to clipboard.

4. ROW_NUMBER() Function

This solves a problem and more which in a curious way related to 3. In SQL 2000 results window you could the row sequence numbers on the frame, but again, those did not make it to the clipboard. In SQL 2005 you can generate row numbers for the entire results set or for partitions within the results set. Oracle had this feature for sometime. Here is an example:

SELECT 'Row Number' = ROW_NUMBER()
OVER(ORDER BY SalesYTD DESC),
c.FirstName, c.LastName,
SalesYTD = '$'+convert(varchar,s.SalesYTD,1),
a.PostalCode
FROM Sales.SalesPerson s
JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0

5. Tree Processing Functions

Probably you did not need tree processing many times during your database development career, but when you needed it, you really needed it. In lieu of it, you resorted to cumbersome loops going from level to level. Of course, tree processing is built-in into C++ and other object-oriented languages. Now you can have it handy in T-SQL when someone asks you to produce an organizational chart. Tree processing is associated with the CTE feature. Here is an example. The organizational chart of AdventureWorks mountain bike manufacturing company:

WITH cteOrgTree(EmployeeID, EmployeeName, ManagerID, TreeLevel, SortOrder)
AS
(
SELECT EmployeeID, EmployeeName=FirstName+' '+LastName, ManagerID, 0,
CAST(EmployeeID AS VARBINARY(1024))
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE ManagerID is null
UNION ALL
SELECT E.EmployeeID, EmployeeName=FirstName+' '+LastName, E.ManagerID, M.TreeLevel+1,
CAST(SortOrder + CAST(E.EmployeeID AS BINARY(4)) AS VARBINARY(1024))
FROM HumanResources.Employee AS E
JOIN cteOrgTree AS M
ON E.ManagerID = M.EmployeeID
JOIN Person.Contact C
ON E.ContactID = C.ContactID
)
SELECT
REPLICATE(' | ', TreeLevel)
+ '(' + (CAST(EmployeeID AS VARCHAR(10))) + ') '
+ EmployeeName AS EmployeeName
FROM cteOrgTree
ORDER BY SortOrder

6. Native XML

The XML integration will surely give a boost in the proliferation of XML as a data format standard. Example for an XQUERY:

SELECT CatalogDescription.query('
declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $pd in /p1:ProductDescription,
$f in $pd/p1:Features/*
return
<Feature>
<ProductModelName> { data($pd/@ProductModelName) } </ProductModelName>
{ $f }
</Feature>
') as Feature
FROM Production.ProductModel
WHERE ProductModelID=25

7. The OUTPUT Clause

Not a very fancy feature, but extremely useful for setting up auditing and logging. It essentially makes the inserted and deleted tables available in the query itself, without resorting to the use of a trigger. Here is an example:

SELECT *
INTO CopyOfEmployee
FROM HumanResources.Employee
GO
DECLARE @DeletedEmployees TABLE
(
EmployeeID INT
)
WHILE (2 > 1)

BEGIN
BEGIN TRAN
DELETE TOP(10) FROM CopyOfEmployee
OUTPUT deleted.EmployeeID
INTO @DeletedEmployees
WHERE HireDate < '2003-02-02'
SELECT * FROM @DeletedEmployees
COMMIT TRAN
DELETE FROM @DeletedEmployees
IF @@rowcount < 10
BREAK
END

8. PIVOT

How many times management came to you for an ad-hoc cross-tab report. You had no choice but setup a temporary table and fill it up or use ackward CASE construct with heavy hard wiring to produce it. PIVOT solves this issue partially, dynamic PIVOT solves it even better. Here is an example:

SELECT pvt.*
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID in ([274],
[238],
[241],
[223],
[264],
[266],
[231],
[244],
[261],
[233],
[164],
[198])
) AS pvt
ORDER BY VendorID

9. Schemas

The idea is that a schema should own db objects since the schema never leaves the company unlike people, especially IT people. SQL Server always had a schema, the infamous "dbo". Another way of looking at schemas is as convenient prefixes for a group of tables. A major shortcoming is that the schemas are single level. In a large database with over 3,000 tables, one would need multi-level schemas. Perhaps that feature is coming with SQL Server 2010.

10. Ranking and Tiling Functions

You can produce tons of Business Intelligence reports using these functions. The more you produce, the more will be requested. Here is an example:

SELECT ProductName = Name, Color, ListPrice,
ROW_NUMBER() OVER(ORDER BY ListPrice DESC) AS SeqNo,
NTILE(6) OVER(ORDER BY ListPrice DESC) AS Sextile,
RANK() OVER(ORDER BY ListPrice DESC) AS Rank
FROM Production.Product
ORDER BY ListPrice DESC