Microsoft pushed SQL Server higher up the evolutionary ladder by significantly improving every area of the new release, including T-SQL. Here are my picks for the seven most useful T-SQL enhancements in SQL Server 2000.
7. New Built-in Functions
SQL Server 2000 provides many new built-in functions that you can use in your T-SQL scripts. The new functions include CHECKSUM(), which calculates a row's or an expression's checksum for use as a hash index; GetUTCDate(), which retrieves the current Universal Time Coordinate (UTC) time; and IDENT_CURRENT('table'), which returns the last identity value that SQL Server generated for the named table.
6. Database and Column-Level Collation
All previous releases of SQL Server use one code page and sort order for the entire server. With SQL Server 2000, each database on the server--and each column in a table--can use a different collation.
5. New Data Types Sql_variant and Bigint
You can use the new sql_variant data type to store any other SQL Server data type except image, text, ntext, or timestamp. The sql_variant data type is useful when you don't know a column's data type in advance or for sparsely populated tables. Bigint, useful for storing identity values, is an 8-bit data type that can hold integer values as large as plus or minus 9,223,372,036,854,775,807.
4. INSTEAD OF Triggers
SQL Server executes INSTEAD OF triggers instead of the action that fired the trigger. You can add INSTEAD OF triggers to a view or table to perform data validation or to update multitable views that would otherwise be read-only. Here's an INSTEAD OF INSERT trigger on the Authors table:
CREATE TRIGGER auInsert ON Authors INSTEAD OF INSERT
3. Table Variables
You can use table variables in place of temporary tables as an alternative way of working with result sets. You can return table-type variables from stored procedures and use them anywhere you can use a standard table type, as in
DECLARE @mytable table(id int, name varchar(20))
2. User-Defined Functions
User-defined functions (UDFs) are multiple-statement T-SQL procedures that accept strongly typed arguments and that can return either table-type or scalar values. The following example creates a UDF called MyName():
CREATE FUNCTION MyName() Returns char(5)
BEGIN
RETURN 'MikeO'
END
1. Cascading DRI
SQL Server 2000 is the first release of SQL Server to support cascading Declarative Referential Integrity (DRI). Cascading DRI cascades updates and deletes from primary-key tables to foreign-key tables.