SQL Server 2005 (code-named "Yukon"), released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using XQuery; SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL. In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data.
SQL Server 2005 also allows a database server to be exposed over web services using TDS packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML.
For relational data, T-SQL has been augmented with error handling features and support for recursive queries. SQL Server 2005 has also been enhanced with new indexing algorithms and better error recovery systems.
Data pages are checksummed for better error resiliency, and optimistic concurrency support has been added for better performance.
Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way.
Partitions on tables and indexes are supported natively, so scaling out a database onto a cluster is easier.
SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.
Four different editions of SQL Server 2005:
SQL Server 2005 Express: replaces the Microsoft Data Engine (MSDE) as the free version of SQL Server for application development and lightweight use. It remains free and retains the limitations of MSDE with respect to client connections and performance. It's a great tool for developing and testing applications and extremely small implementations, but that's about as far as you can run with it.
SQL Server 2005 Workgroup: is the new entrant in the product line. It's billed as a "small business SQL Server" and it offers an impressive array of functionality for a $3,899 price tag per processor. (It's also available under a 5-user license for $739). Workgroup edition maxes out at 2 CPUs with 3GB of RAM and allows for most of the functionality you'd expect from a server-based relational database. It offers limited replication capabilities as well.
SQL Server 2005 Standard Edition remains the staple of the product line for serious database applications. It can handle up to 4 CPUs with an unlimited amount of RAM. Standard Edition 2005 introduces database mirroring and integration services. It's priced at $5,999 for a processor or $2,799 for 5 users.
SQL Server 2005 Enterprise Edition: with the release of 2005, Enterprise Edition allows unlimited scalability and partitioning. It's truly an enterprise-class database and it's hefty price tag ($24,999 per processor or $13,499 for 5 users) reflects its value.