Microsoft SQL Server has shown a lot of growth in its last two releases. With SQL Server 2005, Microsoft continues to aim the tool at enterprise application developers. David Fells examines its most useful new features--and complains about a number of things that Microsoft perhaps shouldn't have done with the program.
Contributed by David Fells Rating: / 40 December 07, 2004
Readers familiar with Microsoft SQL Server are aware of the dramatic feature improvements included with each major release of the product since version 5.0. Driven by the desire to compete with Oracle in enterprise applications, SQL Server has shown a lot of growth with the last two releases, versions 7.0 and 2000, adding such features as Data Transformation Services (DTS)and Extensible Markup Language (XML) support.
With the upcoming release of SQL Server 2005, code named "Yukon," Microsoft adds a slew of new features. The new features are largely geared toward enterprise application developers, which was also the case with SQL Server 2000. Microsoft is making a heavy push for Business Intelligence (BI) reporting services in SQL Server 2005, and as a result, many new features try to solve report writing problems. Developers and database administrators in any environment will benefit from these new features, so without further adieu, ladies and gentlemen: SQL Server 2005.
There is a lot of ground to cover with SQL 2005. Some features warrant much more discussion than others, so a lot of minor updates will be somewhat glossed over. First on our list are the changes to the Transact SQL (TSQL) language.
Recursive Queries and Common Table Expressions
Common Table Expressions (CTE) evolved from SQL Server's derived tables. CTEs are refered to in the FROM clause of a query, just like a derived table or view, but unlike a view, CTEs are non-persistent. CTEs share the life of the full query to which they belong. The big improvement here is support for recursion on, in effect, derived tables. With a derived table, you cannot create it once and use it several times in a query, and it cannot refer to itself in a statement. CTEs overcome this. It's an excellent feature with major potential for report writing. This is one of the many low-level feature additions in the BI push in SQL Server 2005.
PIVOT and UNPIVOT are relational operators that, like CTEs, are geared toward BI applications. To put it briefly, PIVOT provides the basic operation required for crosstabs, effectively converting columns into rows. UNPIVOT does the reverse, converting rows into columns. This allows you to perform quick crosstabs without having to use OLAP. This is a great feature for report building!
APPLY, CROSS APPLY and OUTER APPLY Operators
APPLY is a relational operator that allows you to use a table-valued function once for each row of outer table expressions. You use APPLY in the FROM clause much like a JOIN, and it comes in two forms - CROSS APPLY and OUTER APPLY. CROSS APPLY invokes a table-valued function for each row in an outer table expression. OUTER APPLY is similar but it will return rows from the outer table where the table-valued function returned an empty set. This is a reasonably useful feature, but not life changing for most SQL programmers.
Exception Handling in Transactions
In an effort to render the GOTO statements of previous TSQL implementations useless, SQL Server 2005 implements TRY/CATCH blocks. These blocks work as expected, just like blocks of the same type in C# or VB.NET. This is a good feature for multipart transactions and will definitely demonstrate increased value for developers.
The new features in TSQL offer a lot of benefit for SQL programmers and definitely warrant poking around with the server. Next up is the expanded XML support provided by SQL Server 2005. There is now an XML data type with methods built to operate on that data as well as support for XQuery and DML Extensions.
Standard SQL supports only simple scalar datatypes. XML, on the other hand, is capable of modeling any type of data. The XML datatype allows you to store XML in the database as a supported data type, as opposed to simply storing it as a string.
With the XML data type, you can query, validate, and modify the contents of an XML document in the CML data type. It "integrates traditional, relational data with data in unstructured or semi-structured XML documents in ways that are not possible with SQL Server 2000." I have to take issue with this because SQL is about data storage and retrieval. SQL is not about data processing, especially not XML processing. I'll talk of this more later in the article.
FOR XML and OPENXML Operators, XQuery Support
These allow you to operate on XML data in a tabular format in queries. If you intend to take advantage of the XML data type, you will need to be familiar with these. In combination with these you will use the XQuery support built into Yukon, taking advantage of the methods query(), exist(), value(), nodes(), and modify().
All together, these methods offer the core functionality of XQuery to find and return information from an XML document. Again, if you are a database purist - or more importantly a tiered architecture purist - you will not find yourself using these features often, but for quick and dirty XML solutions these features could be useful.
Next on the tour are the new management and security features. SQL Server made leaps and bounds from version 7.0 to version 2000 in terms of ease of administration, adding support for a wide variety of system notifications and tight integration with the operating systems logging and notification faculties. SQL Server 2005 offers some new management features with SQL Management Objects (SMO), which is the logical continuation of SQL-DMO. It also brings some enhancements to notification, mobile services and security, which I will cover in this area along with SMO.
The SMO model is the management object model for SQL Server 2005. It is based on .NET Framework managed code. It's the primary tool for creating database management applications using the .NET Framework, and is used by every dialog box in the SQL Management Studio.
SMO replaces SQL-DMO but, where possible, implements similar objects for ease of use. SMO has a much larger array of features, however, adding over 150 classes. Microsoft says the big advantages of using SMO are its performance and scalability. It uses a cached object model, meaning you can change multiple object properties before a call to the server is made. This is a nice management feature that will definitely be useful for developers of custom database tools.
New Application Framework - Notifications, Reporting and Mobile Services
I chose to sum these up in one section because the objectives are all similar - to provide enhanced BI and reporting services. The big feature add for notifications and messaging is the SQL Service Broker, built specifically to provide a scalable architecture for building asynchronous message routing. It allows an internal or external process to send and receive streams of reliable, asynchronous messages by using the extensions to normal TSQL data manipulation.
The new reporting services are based on the recently acquired ActiveViews technology to provide developers with a complete set of tools for creating, managing, and viewing reports. They also provide an engine for hosting and processing reports and an open interface based architecture for embedding reports.
The new mobile services allow use of objects similar to the core ADO.NET objects for CE environments, as well as use of DTS and parameterized queries. All together, these make a nice foundation for building business applications - but once again, they don't necessarily belong in the database.
The last and most important change to SQL Server is the integration of the .NET CLR into the database server query environment. According to Microsoft, "Using common language runtime (CLR) integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice." I suppose this is great for .NET programmers, but what about the rest of the world using SQL Server with Java, PHP, COBOL, ADA, etc?
How about this line: "Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code…" The idea here is that developers can perform complex processing on their data before the next application tier ever sees it, but I must ask - what's the point? Those tasks are not database tasks! SQL is for data management and retrieval, not output formatting, mathematical computations, graphics or list processing. Why keep piling on the features? Why dilute SQL with extension upon extension until you have this big mess that does nothing well? Please humor me while I digress.
Author's Opinionated Note:
Apparently Microsoft is encouraging its users to convert SQL Server 2005 into a crude file system and avoid declarative programming entirely. Any programmer worth a week's pay knows that not every tool is suited to every task. Sure, you use a paring knife to skin a pear - but do you use it to hunt a wild boar? Hell no!
SQL exists for a reason - to manage data. That's it! Why are we adding XML datatypes with XQuery support to the RDBMS when it's a middleware task, not a database task? Why are we writing triggers in application layer code in the RDBMS? Integrating the CLR with SQL Server is tantamount to the destruction of tiered architecture. Kiss it goodbye, people.
Let's write all of our data management code in the database - we'll process it, format it, everything - in the database - that is, the store of data - and then our application will simply print it to the screen. Or maybe we will mix it up and do some tasks in the RDBMS and some in the application, or some code in the RDBMS in .NET and other in TSQL. There is literally zero benefit to this sort of thing. TSQL is bad enough without XML and CLR integration - it's proprietary to the point that migration to another RDBMS is not possible and it encourages non-standards compliant SQL.
So for the one or two of you out there working in an environment that only runs .NET and SQL Server on Windows - and will never change or expand - this is great, I guess. For the rest of the world this is just plain stupid. Recite it with me: I will not put application code in my database. I will not put application code in my database. I will not put application code in my database. I'll hold you to it.
To sum it up, SQL Server 2005 offers some great new BI tools and some really nifty enhancements to the Transact SQL language, but overall SQL Server 2005 seems to be a major step backward in terms of application and RDBMS development. Microsoft is taking a proprietary system and making it proprietary to the point that it doesn't even have an identity anymore. This is a perfect case of Microsoft trying to be everything to everyone and trying to apply one tool to every problem - and that, people, is not the way to run a database.
/ 40 
Jasco 2SQL and Salt Lake City Move to SQL Server 2008
