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