Differences Between SQL Server 2000 and 2005

Feature	SQL Server 2000	SQL Server 2005
Server Programming Extensions	Limited to extended stored procedures, which are difficult to write and can impact the server stability.	The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling	Limited to checking @@error, no much flexibility.	Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language	SQL Language enhanced from previous versions providing strong data manipulation capabilities.	All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing	Limited support using triggers to audit changes.	Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types	Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments.	Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML	Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents.	Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET	v1.1 of ADO.NET included enhancements for client development.	v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging	No messaging built into SQL Server.	Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services	An extremely powerful reporting environment, but a 1.0 product.	Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
ETL	DTS is a very easy to use and intuitive tool. Limited capabilities for sources and transformations. Some constructs, such as loops, were very difficult to implement.	Integration Services is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficult for non-DBAs to use. Requires programming skills.
Full-Text Search	Workable solution, but limited in its capabilities. Cumbersome to work with in many situations.	More open architecture, allowing integration and plug-ins of third party extensions. Much more flexible in search capabilities.