Deprecated Database Engine Features in SQL Server 2008 R2

This topic describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2008 R2. These features are scheduled to be removed in a future release of SQL Server. Deprecated features should not be used in new applications.

You can monitor the use of deprecated features by using the SQL Server Deprecated Features Object performance counter and trace events. For more information, see Using SQL Server Objects.

Features Not Supported in the Next Version of SQL Server

The following SQL Server Database Engine features will not be supported in the next version of SQL Server. Do not use these features in new development work, and modify applications that currently use these features as soon as possible. The Feature name value appears in trace events as the ObjectName and in performance counters and sys.dm_os_performance_counters as the instance name. The Feature ID value appears in trace events as the ObjectId.

Category	Deprecated feature	Replacement	Feature name	Feature ID
Backup and restore	BACKUP { DATABASE | LOG } WITH PASSWORD	None	BACKUP DATABASE or LOG WITH PASSWORD	104
Backup and restore	BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD	None	BACKUP DATABASE or LOG WITH MEDIAPASSWORD	103
Backup and Restore	RESTORE { DATABASE | LOG } … WITH DBO_ONLY	RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER	RESTORE DATABASE or LOG WITH DBO_ONLY	101
Backup and restore	RESTORE { DATABASE | LOG } WITH PASSWORD	None	RESTORE DATABASE or LOG WITH PASSWORD	106
Backup and restore	RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD	None	RESTORE DATABASE or LOG WITH MEDIAPASSWORD	105
Compatibility levels	80 compatibility level and upgrade from version 80.	Compatibility levels are only available for the last two versions. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).	Database compatibility level 80	107
Metadata	DATABASEPROPERTY	DATABASEPROPERTYEX	DATABASEPROPERTY	38
Memory Management	Address Windowing Extensions (AWE) support that allows 32-bit operating systems to use over 4 GB of physical memory.	Use a 64-bit operating system to use over 4 GB of physical memory.	None	None
Database objects	WITH APPEND clause on triggers	Re-create the whole trigger.	CREATE TRIGGER WITH APPEND	171
Instance options	Default setting of disallow results from triggers option = 0	Default setting of disallow results from triggers option = 1	sp_configure 'disallow results from triggers'	172
Database options	sp_dboption	ALTER DATABASE	sp_dboption	77
Query hints	FASTFIRSTROW hint	OPTION (FAST n).	FASTFIRSTROW	177
Remote servers	sp_addremotelogin sp_addserver sp_dropremotelogin sp_helpremotelogin sp_remoteoption	Replace remote servers by using linked servers.	sp_addremotelogin sp_addserver sp_dropremotelogin sp_helpremotelogin sp_remoteoption	70 69 71 72 73
Remote servers	@@remserver	Replace remote servers by using linked servers.	None	None
Remote servers	SET REMOTE_PROC_TRANSACTIONS	Replace remote servers by using linked servers.	SET REMOTE_PROC_TRANSACTIONS	110
Security	sp_dropalias	Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases.	sp_dropalias	68
SET options	SET DISABLE_DEF_CNST_CHK	None. Option has no effect.	SET DISABLE_DEF_CNST_CHK	188
SET options	SET ROWCOUNT for INSERT, UPDATE, and DELETE statements	TOP keyword	SET ROWCOUNT	109
Transact-SQL syntax	Use of *= and =*	Use ANSI join syntax. For more information, see FROM (Transact-SQL).	Non-ANSI *= or =* outer join operators	178
Transact-SQL syntax	COMPUTE / COMPUTE BY	Use ROLLUP	COMPUTE [BY]	180
System tables	sys.database_principal_aliases	Use roles instead of aliases.	database_principal_aliases	150
Tools	sqlmaint Utility	Use the SQL Server maintenance plan feature	None	None
Transact-SQL	The old style syntax for RAISERROR (Format: RAISERROR integer string) syntax is deprecated.	Rewrite the statement using the current RAISERROR syntax.	Old style RAISEERROR	164
Programmability	SQL Server Database Management Objects (SQL-DMO)	SQL Server Management Objects (SMO)	SQL Server Database Management Objects (SQL-DMO) has been removed from SQL Server 2008 R2 Express and the SQL Server 2008 R2 Feature Pack. SQL-DMO also does not support Database Engine features introduced after SQL Server 2000. We recommend that you modify applications that currently use this feature as soon as possible. If you must support SQL-DMO, install the Backward Compatibility Components from the SQL Server 2008 Feature Pack from the Microsoft Download Center. Do not use SQL-DMO in new development work; use SQL Server Management Objects (SMO) instead. You can obtain the SMO documentation by installing SQL Server 2008 R2 Books Online.	None

Features Not Supported in a Future Version of SQL Server

The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined.

Category	Deprecated feature	Replacement	Feature name	Feature ID
Compatibility levels	sp_dbcmptlevel	ALTER DATABASE … SET COMPATIBILITY_LEVEL. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).	sp_dbcmptlevel	80
Compatibility levels	Database compatibility level 90	Plan to upgrade the database and application for a future release.	Database compatibility level 90	108
XML	Inline XDR Schema Generation	The XMLDATA directive to the FOR XML option is deprecated. Use XSD generation in the case of RAW and AUTO modes. There is no replacement for the XMLDATA directive in EXPLICT mode.	XMLDATA	181
Backup and restore	BACKUP { DATABASE | LOG } TO TAPE BACKUP { DATABASE | LOG } TO device_that_is_a_tape	BACKUP { DATABASE | LOG } TO DISK BACKUP { DATABASE | LOG } TO device_that_is_a_disk	BACKUP DATABASE or LOG TO TAPE	235
Backup and restore	sp_addumpdevice 'tape'	sp_addumpdevice 'disk'	ADDING TAPE DEVICE	236
Backup and restore	sp_helpdevice	sys.backup_devices	sp_helpdevice	100
Collations	Korean_Wansung_Unicode Lithuanian_Classic SQL_AltDiction_CP1253_CS_AS	None. These collations exist in SQL Server 2005, but are not visible through fn_helpcollations.	Korean_Wansung_Unicode Lithuanian_Classic SQL_AltDiction_CP1253_CS_AS	191 192 194
Collations	Hindi Macedonian	These collations exist in SQL Server 2005 and higher, but are not visible through fn_helpcollations. Use Macedonian_FYROM_90 and Indic_General_90 instead.	Hindi Macedonian	190 193
Collations	Azeri_Latin_90 Azeri_Cyrilllic_90	Azeri_Latin_100 Azeri_Cyrilllic_100	Azeri_Latin_90 Azeri_Cyrilllic_90	232 233
Configuration	SET ANSI_NULLS OFF and ANSI_NULLS OFF database option SET ANSI_PADDING OFF and ANSI_PADDING OFF database option SET CONCAT_NULL_YIELDS_NULL OFF and CONCAT_NULL_YIELDS_NULL OFF database option SET OFFSETS	None. ANSI_NULLS, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL will always be set to ON. SET OFFSETS will be unavailable.	SET ANSI_NULLS OFF SET ANSI_PADDING OFF SET CONCAT_NULL_YIELDS_NULL OFF SET OFFSETS ALTER DATABASE SET ANSI_NULLS OFF ALTER DATABASE SET ANSI_PADDING OFF ALTER DATABASE SET CONCAT_NULL_YIELDS_NULL OFF	SET options
Data types	sp_addtype sp_droptype	CREATE TYPE DROP TYPE	sp_addtype sp_droptype	62 63
Data types	timestamp syntax for rowversion data type	rowversion data type syntax	TIMESTAMP	158
Data types	Ability to insert null values into timestamp columns.	Use a DEFAULT instead.	INSERT NULL into TIMESTAMP columns	179
Data types	'text in row' table option	Use varchar(max), nvarchar(max), and varbinary(max) data types. For more information, see sp_tableoption (Transact-SQL).	Text in row table option	9
Data types	Data types: text ntext image	Use varchar(max), nvarchar(max), and varbinary(max) data types.	Data types: textntext or image	4
Database management	sp_attach_db sp_attach_single_file_db	CREATE DATABASE statement with the FOR ATTACH option. To rebuild multiple log files, when one or more have a new location, use the FOR ATTACH_REBUILD_LOG option.	sp_attach_db sp_attach_single_file_db	81 82
Database objects	CREATE DEFAULT DROP DEFAULT sp_bindefault sp_unbindefault	DEFAULT keyword in CREATE TABLE and ALTER TABLE	CREATE_DROP_DEFAULT sp_bindefault sp_unbindefault	162 64 65
Database objects	CREATE RULE DROP RULE sp_bindrule sp_unbindrule	CHECK keyword in CREATE TABLE and ALTER TABLE	CREATE_DROP_RULE sp_bindrule sp_unbindrule	161 66 67
Database objects	sp_change_users_login	Use ALTER USER.	sp_change_users_login	None
Database objects	sp_depends	sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities	sp_depends	195
Database objects	sp_renamedb	MODIFY NAME in ALTER DATABASE	sp_renamedb	79
Database objects	sp_getbindtoken	Use MARS or distributed transactions.	sp_getbindtoken	98
Database options	sp_bindsession	Use MARS or distributed transactions.	sp_bindsession	97
Database options	sp_resetstatus	ALTER DATABASE SET { ONLINE | EMERGENCY }	sp_resetstatus	83
Database options	TORN_PAGE_DETECTION option of ALTER DATABASE	PAGE_VERIFY TORN_PAGE_DETECTION option of ALTER DATABASE	ALTER DATABASE WITH TORN_PAGE_DETECTION	102
DBCC	DBCC DBREINDEX	REBUILD option of ALTER INDEX.	DBCC DBREINDEX	11
DBCC	DBCC INDEXDEFRAG	REORGANIZE option of ALTER INDEX	DBCC INDEXDEFRAG	18
DBCC	DBCC SHOWCONTIG	sys.dm_db_index_physical_stats	DBCC SHOWCONTIG	10
DBCC	DBCC PINTABLE DBCC UNPINTABLE	Has no effect.	DBCC [UN]PINTABLE	189
Extended properties	Level0type = 'type' and Level0type = 'USER' to add extended properties to level-1 or level-2 type objects.	Use Level0type = 'USER' only to add an extended property directly to a user or role. Use Level0type = 'SCHEMA' to add an extended property to level-1 types such as TABLE or VIEW, or level-2 types such as COLUMN or TRIGGER. For more information, see sp_addextendedproperty.	EXTPROP_LEVEL0TYPE EXTPROP_LEVEL0USER	13 14
Extended stored procedure programming	srv_alloc srv_convert srv_describe srv_getbindtoken srv_got_attention srv_message_handler srv_paramdata srv_paraminfo srv_paramlen srv_parammaxlen srv_paramname srv_paramnumber srv_paramset srv_paramsetoutput srv_paramstatus srv_paramtype srv_pfield srv_pfieldex srv_rpcdb srv_rpcname srv_rpcnumber srv_rpcoptions srv_rpcowner srv_rpcparams srv_senddone srv_sendmsg srv_sendrow srv_setcoldata srv_setcollen srv_setutype srv_willconvert srv_wsendmsg	Use CLR Integration instead.	XP_API	20
Extended stored procedure programming	sp_addextendedproc sp_dropextendedproc sp_helpextendedproc	Use CLR Integration instead.	sp_addextendedproc sp_dropextendedproc sp_helpextendedproc	94 95 96
Extended stored procedures	xp_grantlogin xp_revokelogin xp_loginConfig	Use CREATE LOGIN Use DROP LOGIN IsIntegratedSecurityOnly argument of SERVERPROPERTY	xp_grantlogin xp_revokelogin xp_loginconfig	44 45 59
Functions	fn_get_sql	sys.dm_exec_sql_text	fn_get_sql	151
Index options	sp_indexoption	ALTER INDEX	sp_indexoption	78
Index options	CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options.	Rewrite the statement to use the current syntax.	INDEX_OPTION	33
Database objects	Ability to return result sets from triggers	None	Returning results from trigger	12
Instance options	sp_configure option 'allow updates'	System tables are no longer updatable. Setting has no effect.	sp_configure 'allow updates'	173
Instance options	sp_configure options: 'locks' 'open objects' 'set working set size'	Now automatically configured. Setting has no effect.	sp_configure 'locks' sp_configure 'open objects' sp_configure 'set working set size'	174 175 176
Instance options	sp_configure options 'user instances enabled' and 'user instance timeout'	No longer needed as SQL Server Compact 3.5 SP2 provides the necessary functionality. Setting has no effect.	None	None
Instance options	sp_configure option 'priority boost'	System tables are no longer updatable. Setting has no effect. Use the Windows start /high … program.exe option instead.	sp_configure 'priority boost'	199
Instance options	sp_configure option 'remote proc trans'	System tables are no longer updatable. Setting has no effect.	sp_configure 'remote proc trans'	37
Linked servers	Specifying the SQLOLEDB provider for linked servers.	SQL Server Native Client (SQLNCLI)	SQLOLEDDB for linked servers	19
Locking	sp_lock	sys.dm_tran_locks	sp_lock	99
Mail	SQL Mail	Database Mail	SQLMail	Mail
Metadata	FILE_ID INDEXKEY_PROPERTY	FILE_IDEX sys.index_columns	FILE_ID INDEXKEY_PROPERTY	15 17
Native XML Web Services	The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option. sys.endpoint_webmethods sys.soap_endpoints	Use Windows Communications Foundation (WCF) or ASP.NET instead.	CREATE/ALTER ENDPOINT sys.endpoint_webmethods sys.soap_endpoints	21 22 23
Programmability	The Microsoft.NetEnterpriseServers namespace.	The Microsoft.SqlServer.MessageBox namespace.	None	None
Removable databases	sp_certify_removable sp_create_removable	sp_detach_db	sp_certify_removable sp_create_removable	74 75
Removable databases	sp_dbremove	DROP DATABASE	sp_dbremove	76
Security	The ALTER LOGIN WITH SET CREDENTIAL syntax	Replaced by the new ALTER LOGIN ADD and DROP CREDENTIAL syntax	ALTER LOGIN WITH SET CREDENTIAL	230
Security	sp_addapprole sp_dropapprole	CREATE APPLICATION ROLE DROP APPLICATION ROLE	sp_addapprole sp_dropapprole	53 54
Security	sp_addlogin sp_droplogin	CREATE LOGIN DROP LOGIN	sp_addlogin sp_droplogin	39 40
Security	sp_adduser sp_dropuser	CREATE USER DROP USER	sp_adduser sp_dropuser	49 50
Security	sp_grantdbaccess sp_revokedbaccess	CREATE USER DROP USER	sp_grantdbaccess sp_revokedbaccess	51 52
Security	sp_addrole sp_droprole	CREATE ROLE DROP ROLE	sp_addrole sp_droprole	56 57
Security	sp_approlepassword sp_password	ALTER APPLICATION ROLE ALTER LOGIN	sp_approlepassword sp_password	55 46
Security	sp_changeobjectowner	ALTER SCHEMA or ALTER AUTHORIZATION	sp_changeobjectowner	58
Security	sp_defaultdb sp_defaultlanguage	ALTER LOGIN	sp_defaultdb sp_defaultlanguage	47 48
Security	sp_denylogin sp_grantlogin sp_revokelogin	ALTER LOGIN DISABLE CREATE LOGIN DROP LOGIN	sp_denylogin sp_grantlogin sp_revokelogin	42 41 43
Security	USER_ID	DATABASE_PRINCIPAL_ID	USER_ID	16
Security	sp_srvrolepermission sp_dbfixedrolepermission	These stored procedures return information that was correct in SQL Server 2000. The output does not reflect changes to the permissions hierarchy implemented in SQL Server 2008. For more information, see Permissions of Fixed Server Roles.	sp_srvrolepermission sp_dbfixedrolepermission	61 60
Security	GRANT ALL DENY ALL REVOKE ALL	GRANT, DENY, and REVOKE specific permissions.	ALL Permission	35
Security	PERMISSIONS intrinsic function	Query sys.fn_my_permissions instead.	PERMISSIONS	170
Security	SETUSER	EXECUTE AS	SETUSER	165
Security	RC4 and DESX encryption algorithms	Use another algorithm such as AES.	DESX algorithm	238
SMO classes	Microsoft.SQLServer.Management.Smo.Information class Microsoft.SQLServer.Management.Smo.Settings class Microsoft.SQLServer.Management.Smo.DatabaseOptions class Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger.NotForReplication property	Microsoft.SqlServer.Management.Smo.Server class Microsoft.SqlServer.Management.Smo.Server class Microsoft.SqlServer.Management.Smo.Database class None	None	None
SQL Server Agent	net send notification Pager notification ActiveX subsystem	E-mail notification E-mail notification Command or PowerShell scripts	None	None
SQL Server Management Studio	Solution Explorer integration in SQL Server Management Studio Source Control integration in SQL Server Management Studio		None	None
System tables	sysaltfiles syscacheobjects syscolumns syscomments sysconfigures sysconstraints syscurconfigs sysdatabases sysdepends sysdevices sysfilegroups sysfiles sysforeignkeys sysfulltextcatalogs sysindexes sysindexkeys syslockinfo syslogins sysmembers sysmessages sysobjects sysoledbusers sysopentapes sysperfinfo syspermissions sysprocesses sysprotects sysreferences sysremotelogins sysservers systypes sysusers	Compatibility views. For more information, see Compatibility Views (Transact-SQL). Important The compatibility views do not expose metadata for features that were introduced in SQL Server 2005. We recommend that you upgrade your applications to use catalog views. For more information, see Catalog Views (Transact-SQL).	sysaltfiles syscacheobjects syscolumns syscomments sysconfigures sysconstraints syscurconfigs sysdatabases sysdepends sysdevices sysfilegroups sysfiles sysforeignkeys sysfulltextcatalogs sysindexes sysindexkeys syslockinfo syslogins sysmembers sysmessages sysobjects sysoledbusers sysopentapes sysperfinfo syspermissions sysprocesses sysprotects sysreferences sysremotelogins sysservers systypes sysusers	141 152 None 133 126 146 131 147 142 123 144 128 127 130 122 132 134 None 143 140 119 137 125 139 145 157 121 153 120 129 138 136 135 124
System tables	sys.numbered_procedures sys.numbered_procedure_parameters	None	numbered_procedures numbered_procedure_parameters	148 149
System functions	fn_virtualservernodes fn_servershareddrives	sys.dm_os_cluster_nodes sys.dm_io_cluster_shared_drives	fn_virtualservernodes fn_servershareddrives	155 156
System views	sys.sql_dependencies	sys.sql_expression_dependencies	sys.sql_dependencies	196
Table compression	The use of the vardecimal storage format.	Vardecimal storage format is deprecated. SQL Server 2008 data compression compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage format.	Vardecimal storage format	200
Table compression	Use of the sp_db_vardecimal_storage_format procedure.	Vardecimal storage format is deprecated. SQL Server 2008 data compression compresses decimal values as well as other data types. We recommend that you use data compression instead of the vardecimal storage format.	sp_db_vardecimal_storage_format	201
Table compression	Use of the sp_estimated_rowsize_reduction_for_vardecimal procedure.	Use data compression and the sp_estimate_data_compression_savings procedure instead.	sp_estimated_rowsize_reduction_for_vardecimal	202
Table hints	Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement.	Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.	NOLOCK or READUNCOMMITTED in UPDATE or DELETE	None
Table hints	Specifying table hints without using the WITH keyword.	Use WITH.	Table hint without WITH	8
Table hints			HOLDLOCK table hint without parenthesis	167
Table hints			INSERT_HINTS	34
Textpointers	WRITETEXT UPDATETEXT READTEXT	None	UPDATETEXT or WRITETEXT READTEXT	115 114
Textpointers	TEXTPTR() TEXTVALID()	None	TEXTPTR TEXTVALID	5 6
Transact-SQL	:: function-calling sequence	Replaced by SELECT column_list FROM sys.<function_name>(). For example, replace SELECT * FROM ::fn_virtualfilestats(2,1)with SELECT * FROM sys.fn_virtualfilestats(2,1).	'::' function calling syntax	166
Transact-SQL	Three-part and four-part column references.	Two-part names is the standard-compliant behavior.	More than two-part column name	3
Transact-SQL	A string enclosed in quotation marks used as a column alias for an expression in a SELECT list: 'string_alias' = expression	expression [AS] column_alias expression [AS] [column_alias] expression [AS] "column_alias" expression [AS] 'column_alias' column_alias = expression	String literals as column aliases	184
Transact-SQL	Numbered procedures	None. Do not use.	ProcNums	160
Transact-SQL	table_name.index_name syntax in DROP INDEX	index_name ON table_name syntax in DROP INDEX.	DROP INDEX with two-part name	163
Transact-SQL	Not ending Transact-SQL statements with a semicolon.	End Transact-SQL statements with a semicolon ( ; ).	None	None
Transact-SQL	GROUP BY ALL	Use custom case-by-case solution with UNION or derived table.	GROUP BY ALL	169
Transact-SQL	ROWGUIDCOL as a column name in DML statements.	Use $rowguid.	ROWGUIDCOL	182
Transact-SQL	IDENTITYCOL as a column name in DML statements.	Use $identity.	IDENTITYCOL	183
Transact-SQL	Use of #, ## as temporary table and temporary stored procedure names.	Use at least one additional character.	'#' and '##' as the name of temporary tables and stored procedures	None
Transact-SQL	Use of @, @@, or @@ as Transact-SQL identifiers.	Do not use @ or @@ or names that begin with @@ as identifiers.	'@' and names that start with '@@' as Transact-SQL identifiers	None.
Transact-SQL	Use of DEFAULT keyword as default value.	Do not use the word DEFAULT as a default value.	DEFAULT keyword as a default value	187
Transact-SQL	Use of a space as a separator between table hints.	Use a comma to separate table hints.	Multiple table hints without comma	168
Transact-SQL	The select list of an aggregate indexed view must contain COUNT_BIG (*) in 90 compatibility mode	Use COUNT_BIG (*).	Index view select list without COUNT_BIG(*)	2
Transact-SQL	The indirect application of table hints to an invocation of a multi-statement table-valued function (TVF) through a view.	None.	Indirect TVF hints	7
Transact-SQL	ALTER DATABASE syntax: MODIFY FILEGROUP READONLY MODIFY FILEGROUP READWRITE	MODIFY FILEGROUP READ_ONLY MODIFY FILEGROUP READ_WRITE	MODIFY FILEGROUP READONLY MODIFY FILEGROUP READWRITE	195 196
Other	DB-Library Embedded SQL for C	Although the Database Engine still supports connections from existing applications that use the DB-Library and Embedded SQL APIs, it does not include the files or documentation required to do programming work on applications that use these APIs. A future version of the SQL Server Database Engine will drop support for connections from DB-Library or Embedded SQL applications. Do not use DB-Library or Embedded SQL to develop new applications. Remove any dependencies on either DB-Library or Embedded SQL when you are modifying existing applications. Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2008 does not include the DB-Library DLL required to run these applications. To run DB-Library or Embedded SQL applications, you must have available the DB-Library DLL from SQL Server version 6.5, SQL Server 7.0, or SQL Server 2000.	None	None
Other	VIA protocol	Use TCP.	None	None

See Also

Other Resources

Discontinued Database Engine Functionality in SQL Server 2008 R2

Change History

Updated content
SQL Server Database Management Objects (SQL-DMO) moved to the table of features not supported in the next version.