What's New and Cool in SQL Server "Yukon"
A Developer's Tale
Sure, SQL Server has lots of features that make it easy to manage and administer. Sure, it's a world-class relational database management system. Sure, Transact-SQL (T-SQL) is an easy-to-learn language. Yet despite these and other facts, many developers only have a cursory knowledge of programming in SQL Server. I am here to announce that all that will change with SQL Server "Yukon".
After years in the making, Microsoft is going to begin a new era of database programmability with its introduction of SQL Server "Yukon". The changes include enhancements to some familiar features, such as T-SQL and DTS, and some new additions, including new object models, .NET integration, and some new services, such as the SQL Service Broker and Reporting Services. We are going to touch on a variety of these enhancements and new features in order to give you a taste of what's to come.
T-SQL introduces a variety of new and enhanced features. The majority of the changes affect the ways in which a developer can manipulate data, which is of course, the main reason for using T-SQL code in the first place. Without going into too much detail, I'd like to highlight some of the big changes to the language set.
T-SQL has several new data types, including native handling for XML data, which is covered in the following section. There are also three new data-related data types: Date, Time and UtcDateTime. These new types have better accuracy and range than their existing counterparts, DateTime and SmallDateTime. They are also special implementations of .NET user-defined data types, and thus they have several special properties and methods that allow you to get portions of the date or time and even convert it to a string using a user-specified format.
T-SQL now has the ability to pivot and unpivot data. No more dynamic T-SQL or overly complex SELECT statements — now you can use this new ability to create crosstabulated data results. These two SELECT statements will give the same results, but obviously, the second is a lot easier to write, especially if we are dealing with more than three pivoted output columns.
SELECT ProductID, ProductName, SUM(CASE TheYear WHEN 1996 THEN Extended ELSE 0 END) AS , SUM(CASE TheYear WHEN 1997 THEN Extended ELSE 0 END) AS , SUM(CASE TheYear WHEN 1998 THEN Extended ELSE 0 END) AS FROM vwOrderDetailsGROUP BY ProductID, ProductNameSELECT * FROM vwOrderDetailsPIVOT (SUM(Extended) FOR TheYear IN (, , )) AS PVT
Common Table Expressions are new to SQL Server "Yukon" as well. At first, they appear to be another means of creating derived-tables, and they do indeed possess many of the same qualities. But, more in-depth research shows that they also give us the ability to write recursive queries. So if we want to iterate up or down a hierarchal table structure, we can create a query that references itself and will recurse as needed (with a default maximum of 100 levels deep) or until we specify otherwise. The following example uses the Employee table in the Northwind database to demonstrate how to find all employees and their managers, and those managers' managers, etc....
WITH EmpCTE (EmpID, EmpName, MgrID, Level)AS (SELECT E.EmployeeID, E.LastName, E.ReportsTo, 1 FROM Employees E WHERE ReportsTo IS NULL UNION ALL SELECT E.EmployeeID, E.LastName, E.ReportsTo, Level + 1 FROM Employees E INNER JOIN EmpCTE ON EmpCTE.EmpID = E.ReportsTo WHERE Level <= 5)SELECT EmpID, EmpName, MgrID, LevelFROM EmpCTE
T-SQL now also has an improved exception handling mechanism, using the TRY...CATCH mechanism seen in .NET languages. Although not nearly as robust, it does allow the developer to catch many problems and thus handle issues that were once only manageable in the data access layer of code.
There are many other new features not mentioned here because of the space limitations. Of course, there will be lots of places to find out more, including various online sources, magazines and books. All I can say is, in my humble opinion, you will be quite happy with the changes.