SQL Server 2000 is by far the best version of SQL Server yet! The major new features included support: multiple instances, interface changes, new data types, text in row, indexed views, after and instead of triggers, cascade update and delete, user-defined functions, and XML support.
SQL Server 2000's support of multiple instances means that multiple SQL Server services can run on a single server machine. You use one set of client tools to manage these instances. Advantages of this feature are the ability to maintain older versions of SQL Server, and to simulate multiple servers for testing.
SQL Server 2000 sports a much easier-to-use interface than its predecessors-which is particularly apparent in the table designer and query analyzer. SQL Server 2000 introduces three new data types: Bigint, Sql_variant, and Table. The new "text in row" feature allows you to store text, ntext, and image data in the same pages as your other data...which reduces both disk I/O and storage space, while improving performance.
Indexed views allow you to store a "result set" as part of the database. This feature is great for data warehousing, and significantly enhances performance in any data warehouse situation.
SQL Server 2000 allows you to designate the first and last "after triggers" to execute. New to SQL Server 2000 is "instead of triggers." These triggers execute prior to checking constraints. They have many uses, including allowing you to set default values prior to constraints being checked.
You can use the new "cascade update" feature to update a foreign key value when a primary key value changes-or use the new "cascade delete" feature to eliminate child records when the parent row is removed.
User-defined functions allow you to build your own out of T-SQL functions and statements. They can receive one or more parameters, and return one value. They also promote reusability and can ultimately replace stored procedures.
Finally, SQL Server 2000 has very strong XML support. As you can see, the features new to SQL Server 2000 are vast and varied-and they all serve to facilitate administration and improve performance.