New and Enhanced Features in Transact-SQL

Transact-SQL Reference

Transact-SQL Reference

New and Enhanced Features in Transact-SQL

Transact-SQL in Microsoft® SQL Server™ 2000 provides new and enhanced statements, stored procedures, functions, data types, DBCC statements, and information schema views.

Data Types
New data types
bigint table
sql_variant  
Database Console Commands (DBCC)
New commands
DBCC CHECKCONSTRAINTS DBCC DROPCLEANBUFFERS
DBCC CLEANTABLE DBCC FREEPROCCACHE
DBCC CONCURRENCYVIOLATION DBCC INDEXDEFRAG

Enhanced commands
DBCC CHECKALLOC DBCC CHECKFILEGROUP
DBCC CHECKDB DBCC SHOWCONTIG
DBCC CHECKTABLE  

Functions
New functions
BINARY_CHECKSUM fn_virtualfilestats
CHECKSUM GETUTCDATE
CHECKSUM_AGG HAS_DBACCESS
COLLATIONPROPERTY IDENT_CURRENT
COUNT_BIG INDEXKEY_PROPERTY
DATABASEPROPERTYEX OBJECTPROPERTY
fn_helpcollations OPENDATASOURCE
fn_listextendedproperty OPENXML
fn_servershareddrives ROWCOUNT_BIG
fn_trace_geteventinfo SCOPE_IDENTITY
fn_trace_getfilterinfo SERVERPROPERTY
fn_trace_getinfo SESSIONPROPERTY
fn_trace_gettable SQL_VARIANT_PROPERTY

Information Schema Views
New information schema views
PARAMETERS ROUTINE_COLUMNS
ROUTINES  

Replication Stored Procedures
New replication stored procedures
sp_addmergealternatepublisher sp_getqueuedrows
sp_addscriptexec sp_getsubscriptiondtspackagename
sp_adjustpublisheridentityrange sp_helparticledts
sp_attachsubscription sp_helpmergealternatepublisher
sp_browsesnapshotfolder sp_helpreplicationoption
sp_browsemergesnapshotfolder sp_ivindexhasnullcols
sp_changesubscriptiondtsinfo sp_marksubscriptionvalidation
sp_copysnapshot sp_mergearticlecolumn
sp_disableagentoffload sp_repladdcolumn
sp_dropanonymouseagent sp_repldropcolumn
sp_dropmergealternatepublisher sp_restoredbreplication
sp_enableagentoffload sp_resyncmergesubscription
sp_getagentoffloadinfo sp_vupgrade_replication

Reserved Keywords

COLLATE, FUNCTION, and OPENXML are reserved keywords in SQL Server 2000.

The following words have been unreserved.

AVG COMMITTED
CONFIRM CONTROLROW
COUNT ERROREXIT
FLOPPY ISOLATION
LEVEL MAX
MIN MIRROREXIT
ONCE ONLY
PERM PERMANENT
PIPE PREPARE
PRIVILEGES REPEATABLE
SERIALIZABLE SUM
TAPE TEMP
TEMPORARY UNCOMMITTED
WORK  

Statements
New statements
ALTER FUNCTION DROP FUNCTION
CREATE FUNCTION  

Enhanced statements
ALTER DATABASE CREATE TABLE
ALTER TABLE CREATE TRIGGER
BACKUP INDEXPROPERTY
COLUMNPROPERTY OBJECTPROPERTY
CREATE INDEX RESTORE
CREATE STATISTICS  

System Stored Procedures
New system stored procedures
sp_addextendedproperty sp_delete_maintenance_plan_job
sp_add_log_shipping_database sp_dropextendedproperty
sp_add_log_shipping_plan sp_get_log_shipping_monitor_info
sp_add_log_shipping_plan_database sp_helpconstraint
sp_add_log_shipping_primary sp_helpindex
sp_add_log_shipping_secondary sp_help_maintenance_plan
sp_add_maintenance_plan sp_invalidate_textptr
sp_add_maintenance_plan_db sp_remove_log_shipping_monitor
sp_add_maintenance_plan_job sp_resolve_logins
sp_can_tlog_be_applied sp_settriggerorder
sp_change_monitor_role sp_trace_create
sp_change_primary_role sp_trace_generateevent
sp_change_secondary_role sp_trace_setevent
sp_create_log_shipping_monitor_account sp_trace_setfilter
sp_define_log_shipping_monitor sp_trace_setstatus
sp_delete_log_shipping_database sp_updateextendedproperty
sp_delete_log_shipping_plan sp_update_log_shipping_monitor_info
sp_delete_log_shipping_plan_database sp_update_log_shipping_plan
sp_delete_log_shipping_primary sp_update_log_shipping_plan_database
sp_delete_log_shipping_secondary sp_xml_preparedocument
sp_delete_maintenance_plan sp_xml_removedocument
sp_delete_maintenance_plan_db  

Enhanced system stored procedures
sp_helptrigger sp_serveroption
sp_tableoption sp_who

System Tables
New system tables
logmarkhistory MSsync_states
log_shipping_databases sysdbmaintplan_databases
log_shipping_monitor sysdbmaintplan_history
log_shipping_plan_databases sysdbmaintplan_jobs
log_shipping_plan_history sysdbmaintplans
log_shipping_plans sysmergeschemaarticles
log_shipping_secondaries sysopentapes
Mssub_identity_range