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:
|
Use varchar(max), nvarchar(max), and varbinary(max) data types.
|
Data types: text ntext 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 (Transact-SQL).
|
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 SP1 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.
|
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.
EXT_endpoint_webmethods
EXT_soap_endpoints
|
Use Windows Communications Foundation (WCF) or ASP.NET instead.
|
EXT_CREATE_ALTER_SOAP_ENDPOINT
EXT_endpoint_webmethods
EXT_soap_endpoints
|
21
22
23
|
|
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 Express and will be removed from other editions.
We recommend that you modify applications that currently use this feature as soon as possible. If you must support SQL-DMO for SQL Server Express, install the Backward Compatibility Components from the SQL Server 2005 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 2005 Books Online.
|
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 (Database Engine).
|
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 encryption algorithm
|
Use another algorithm such as AES.
|
SETUSER
|
165
|
|
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).
.gif) 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 in SELECT list
|
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 using a statement terminator for Transact-SQL statements.
|
End Transact-SQL statements with a statement terminator, which is 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
|