Microsoft's SQL Server 2005

To test Management Studio, I created a database with tables and other objects. Unlike those in older versions, most dialog boxes in the IDE aren't modal, so you needn't cancel out of one to get to the main console. Plus, Management Studio lets you script any action on the fly. I created a database and tables while scripting them. This capability should improve user productivity.

Among other enhancements, SQL Profiler saves a Trace file as XML so it can be modified and reused. And the Database Tuning Advisor takes into account user-defined functions and statements in triggers.

T-SQL Developments

On the database development front, SQL Server 2005 provides more T-SQL (Microsoft's extension to SQL) functionality--enhancements that fill in some of the holes found in SQL Server 2000. T-SQL now has a ranking function, for example, that lets you return a specific group of records from a large query. With 2000, developers had to create temporary tables, then filter out what wasn't needed--not very efficient. I tested this functionality by adding the ROW_NUMBER function to a standard query to return a subset of the rows. It worked.

T-SQL also includes the standard TRY-CATCH construct for robust error handling. This is much more user-friendly than 2000's requirement to check the @@Error variable after each line of code.

New to T-SQL are common table expressions (CTEs) that let you specify a temporary named result set to use within your query. CTEs can reference themselves, letting you define recursive queries. I easily wrote a query that returned hierarchical data.Despite all these goodies, T-SQL's new Pivot command isn't ideal. I generated a cross-tab query by defining columns, but I couldn't get SQL to dynamically determine the columns to pivot. A truly functional pivot would deliver full cross-tab queries, but since the Pivot command requires you to define what to pivot, it comes up short.

XML Support

SQL Server 2005 includes much more support for XML data than 2000, including a new XML data type that can hold XML documents or XML fragments (XML without a top-level element) to be stored in a column, parameter or variable. The XML data can be retrieved or updated with XQuery or XML DML (Data Modification Language). After writing a few XQuery queries against the included Adventureworks sample database, I found this functionality powerful, but you must have a good understanding of XPath and XQuery and write large queries.

Microsoft has also enhanced the FOR XML clause, which is used to query relational data from SQL Server in XML format. FOR XML clauses can be nested, so you can produce a combination of element- and attribute-type XML. I found it easy to return hierarchical XML data by nesting the queries in the hierarchy I needed. I could then add a FOR XML clause to each level to create elements or attributes in the XML output.

Integrated .Net SupportSQL Server is now integrated with .Net CLR (Common Language Runtime), which controls tasks such as memory management and code compilation. This gives SQL Server the power of full-fledged development languages like VB.NET and C#.NET to create stored procedures, triggers and user-defined functions.

To test this, I wrote and deployed a function in Visual Studio 2005 in just a few minutes. I was then able to use the function in a SQL statement just as you would use a built-in SQL function. Although this feature gives more power to stored procedures, T-SQL should be used instead when possible because it doesn't have as much overhead as CLR. The CLR is just right, however, when T-SQL cannot handle a certain function, such as writing to the file system.

Keep It Safe

Microsoft's security problems keep it vigilant. The new default installation of SQL Server 2005 disables many services and features that were typically enabled in SQL Server 2000, including remote named pipes, TCP and HTTP connections, OLE automation and the xp_cmdshell extended stored procedure. Although these are disabled by default, Microsoft says an upgrade installation will leave enabled most of what you had enabled. Also, these services and protocols can be managed using the new Surface Area Configuration tool.

Cory Cundy is a applications developer for a software company. A Microsoft Certified Professional, his expertise is in developing applications using the .Net framework and related Microsoft products. Write to him at [email protected].0