Top 30 New Features of SQL Server 2005
By Kalman Toth, M.Phil., M.Phil., MCDBA

SQL Server 2005 is much better than SQL Server 2000. True Microsoft had 5 years to make it better so what is the big deal? Well the big deal is an excellent enterprise-level product. Based on my 16 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 for programming, administration or operations. Albeit sometimes the lines are blurred: a new programming feature may turn out to be an operational godsend as well. Some of the new features are revolutionary, such as CTE (Common Table Expression) with tree processing, others already existed in the industry for example the ROW_NUMBER function in Oracle.

1. Management Studio. It is heavy duty, much better, more reliable than the Enterprise Manager/Query Analyzer duo in SQL Server 2000. One negative: SSIS (DTS successor) is not included. For marketing purposes Microsoft moved it into Business Intelligence Studio.

2. 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 were 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.

3. 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

4. SSIS. It is a really superior replacement for DTS with lots of new features and excellent error logging. A must tool for data warehouse ETL.

5. Database Mirroring. Dubbed as "Poor man's Clustering" provides few seconds automatic switchover to standby server. Much improved over log shipping where failover typically took ½ - 1 hour. There are several modes of operation: automatic, manual and forced failover. In a way it is even better than clustering because two independent servers operating in parallel. It does not come with virtual IP, so you have to provide that from a third-party source.

6. Reporting Services. It is a full-blown Business Intelligence delivery layer. Will be popular with shops that don't want to spend big bucks with BI vendors.

7. Indexes including non-indexed columns. This feature can be a make or fail feature in several applications.

8. Deadlock visualizer. Really takes the mystery out of deadlocks. Clearly show the participant queries, dead and surviving.

9. Column-level data encryption/decryption. Long time missing, but finally here. High-powered encryption like 3DES made real easy.

10. Dynamic Management Views & Functions
Substantial collection of systems views and function which provide real-time operational data about the server and the processes currently executing. Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

11. 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

12. 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

13. Improved Performance. Generally 20-40% improvement over SQL Server 2000. Actual gains depend on implementation of such features as table partitioning, non-index columns in indexes, no-block reindexing (Enterprise level only), TRY-CATCH and more.

14. Analysis Services. It has a great number of improvements over SQL Server 2000 AS. Among the new features, OLAP cube creation/editing in Business Intelligence Development Studio, improved security, improved ETL tool (SSIS), new cube viewer, cubes can be based on multiple fact tables, support for the Unified Dimensional Model, MDX improvements and new data mining tools.

15. Business Intelligence Development Studio. It is a unified environment to build BI based on SSAS, SSIS and SSRS. Intensive graphical environment to create sophisticated OLAP cubes, data mining structures, SSIS packages and reports.

16. 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

17. Correlating perfmon chart with profiler trace. This feature makes finding the performance hog "culprit" real easy.

18. Event notification on server events such as logins. Like a dream come true, futuristic feature for administrators.

19. SQLCMD. This is truly super-duper enhancement of OSQL with true scripting language.

20. 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 awkward 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

21. Online Reindexing. This is a real miracle improvement for 24/7 shops. Users won't be blocked anymore why a table is being totally reindexed usually with a FILL FACTOR for next day's operations. This is Enterprise Level feature.

22. Security Improvements. Major functionality improvements in security will help large organizations to setup their environments appropriately. One example for improvement: database object visibility can be restricted. For small organizations even SQL Server 2000 security facilities are too overwhelming.

23. Service Broker. Asynchronous queuing facility is a freebie. It is an application add-on that can be used to build database administration tools or business applications.

24. Event Notification. Also an application add-on, can be used to build system or application near real-time notification. A system notification maybe a table dropped in a database. An application notification maybe an order placed with the company.

25. Table/Index Partitioning. Really helpful when you are operating with tables over 100 million rows or over 10GB in size.

26. 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.

27. Windowing Functions: ROW_NUMBER, Ranking and Tiling

ROW_NUMBER() Function
This solves a problem and more that 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

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

28. Configuration Manager. This tool unites several distinct operational management components of SQL Server 2005 such as services manager and network configuration.

29. Memory Support. The Standard and Enterprise Edition provides for unlimited memory support. Memory, being cheap, can be used to boost server performance. More data pages and stored procedures with compiled execution plans can stay memory resident thus increasing server performance.

30. 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.