• Full support for cascade deletes and updates
• User Defined Functions - written in TSQL. To index on a user defined function it must be created with the WITH SCHEMABINDING option and it must be deterministic. But deterministic UDF’s cannot reference any tables, views etc.
  Strongly typed input arguments – no output parameters. Can return scalar or table value.
  In-line table is better for performance as it can be expanded in the query for integrated optimization. Effectively it is performing the function of a parameterized view.
  Table valued UDF. The header contains the table variable declaration. It is then inserted/deleted etc. by the function. At the end of the function whatever is in the table is returned.
  UDF cannot update tables, global cursors, DDL, transactional statements
  Can update table variables and use local cursors
  Must use a 2-part name to call a scalar function (in case a user defines a function with the same name as a system function)
  Can put UDF in SELECT list (WHERE), CHECK constraint, DEFAULT definition.
  Table UDF’s can be put in a FROM clause.
• ALTER DATABASE pubs SET SINGLE_USER with ROLLBACK IMMEDIATE – kicks everyone out straight away. Or can give them some leeway.
• ALTER DATABASE command to quiesce database and not allow any new connections.
• More parallelism features, but some only work when running Enterprise Edition.
• More "trivial" queries are optimized at the first level of the optimizer – e.g. simple joins.
• Parallel plan costs and serial plan costs are now directly comparable. (Before the numbers used were different and could not be compared)
• Variant data-type – like in VB. Can store any SQL type except BLOB/TEXT. Need to convert to base value before comparing/operating on it.
• Table-type variable. Used for table return values from UDF’s. Can be used instead of a temp table. Can’t create an index on a table variable (other than those implied by a PK, Unique constraint etc.). Scope is the current module as opposed to the rest of the session for temp tables. Not recompiled on reference like a temp table is in an sp.
  DECLARE @T TABLE (id int, name varchar(8))
  INSERT @T values(10,’Joe’)
  select id from @T
• BigInt type. 8 byte integer. COUNT_BIG() function uses. COUNT uses INT.
• "Instead of" trigger gives before trigger functionality. Can be applied to tables or views.
• Triggers can now be ordered
• ALTER DATABASE MODIFY FILE to change the logical name of files
• KILL command feeds back the status of the rollback – i.e. 20% way through rollback.
• Index creation moved into query processor to take advantage of parallelism. A single index can be created in parallel using multiple threads. Good for very large indexes.
• Building an index will now use other indexes if necessary rather than just doing a table scan (assuming there are some common columns).
• Checksum function to work over one or more columns
• Enhanced intra-query parallelism. TPC-H improved 40% over SQL 7.0
• Server-side cursors – both compile and execution plan cached. 10-15% reduction in cpu for cursors. No app changes needed.
• SP parameters are cached and re-used. On big sp’s with many parameters helps with cpu. SAP performance improved 6% over SQL 7.0. As long as are using prepare/execute for sp then no app changes are needed.
• Getutcdate() – new function to return current UTC time
• SCOPE_IDENTITY(). Last identity value in current scope (doesn’t worry about a trigger that updated another table with an identity column)
• IDENT_CURRENT(‘tblname’) – last identity value generated for a table
• CONTEXT_INFO() – A per-session value that can be set with SET CONTEXT_INFO. It’s a binary value.
• ALTER DATABASE now used for setting database options – sp_dboption now calls this.
• DBCC’s will run on parallel threads – even faster. Scales at > 80% per cpu.
• Can optionally fire triggers during a bulk load
• Can check constraints after loading (for speed) if you like.
• Bulk loads will now scale almost linearly for concurrent loads – 1 per processor.

SQL Server 2000 Preview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19