Microsoft SQL Server 2005

It's five years since the last version of SQL Server, and in that time Microsoft has prepared a bumper crop of new features and enhancements to delight DBAs, developers and managers. There's a new front end, new features in the back end and new services on the side.

DB ENGINE

The biggest addition to SQL Server 2005 is the ability to write stored procedures, functions, triggers, user-defined types and aggregates in any .NET language such as VB .NET or C#. Transact-SQL, the language usually used with SQL Server, is great for set-based operations but not so good for general programming tasks because it's so specialised. Languages using .NET have constructs and abilities that aren't found in T-SQL. You also have full access to all the namespaces and classes in the .NET Framework. VB .NET or C# code is also subject to all the CLR security, memory management and type-safe checking, so it isn't capable of compromising the security or integrity of SQL Server in the way extended stored procedures (XPs) have been in the past. To write a VB .NET or C# stored procedure or trigger, you use Visual Studio 2005 to create a new project from the SQL Server template. This creates the assembly and manifest needed for your code and uploads it to the server when you're ready to deploy it.

While you can now write stored procedures in VB .NET or C#, that doesn't mean you have to or should: T-SQL is still the best language to choose if you want to work with sets of data in the traditional way.

In SQL Server 2005, you can define a column as containing XML data, optionally associating an XML schema with the column. You can also create variables or parameters of type XML. If you specify a schema, the XML fragment or documents you store are validated against that. You can also specify XML-based constraints by creating user-defined functions that return TRUE/FALSE based on an XML parameter.

You can query data in XML columns using the XQuery language. This is an extension to the XPath language but has better iteration and sorting. XQuery is still under development by the W3C (World Wide Web Consortium), and SQL Server implements a version of XQuery aligned with the July 2004 Working Draft. Unfortunately, W3C released another Working Draft in September 2005, but that isn't even a Candidate Recommendation, let alone a standard. Microsoft has extended the XQuery language to allow document fragments to be inserted, updated and deleted.

XML documents and fragments stored in XML columns can be up to 2GB each. Large documents would be time-consuming to search using T-SQL and XQuery, so SQL Server allows XML columns to be indexed to speed up access. These indexes shred the XML into nodes and values to give quick access to rows where a node exists or rows where a node has a particular value.

SQL Server 2005 can provide direct access to stored procedures, defined functions and batch statements as Web Services using HTTP, SOAP and WSDL without requiring SQLXML, ODBC or TDS. This gives greater access to SQL Server in heterogeneous environments, better integration from Visual Studio 2005, JBuilder and other IDEs, improved support for mobile devices and other 'occasionally connected' computers, and better security without the need to implement additional firewalls. The CREATE ENDPOINT command sets up web addresses to which SQL Server will monitor requests and stored procedures or functions available at that address.

Microsoft has made many enhancements to SQL Server to improve its already impressive uptime. Creating, altering or dropping indexes or even restoring a database shouldn't stop users from using the database at the same time. When recovering a database, users get partial or full access to recovered data before recovery is fully complete. Rather than stop at the first error, restoration will continue if there are errors and, if a database is marked as suspect, members of the sysadmin group will still be allowed read-only access to help diagnose the problem. Just in case things go badly wrong and the server can't accept any new connections, there's now a dedicated connection for sysadmins to use to diagnose and correct problems.