Deprecated Database Engine Features in SQL Server 2005
Deprecated features include features that will not be supported in the next version of SQL Server and features that will not be supported in a future version of SQL Server.
Features Not Supported in the Next Version of SQL Server
These Database Engine features will not be supported in the next version of SQL Server. We recommend that, as time allows, you replace these features with the replacement item if possible.
| Category | Deprecated feature | Replacement |
|---|---|---|
Backup and restore |
DUMP statement |
BACKUP |
Backup and restore |
LOAD statement |
RESTORE |
Backup and restore |
BACKUP LOG WITH NO_LOG |
None. The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model. |
Backup and restore |
BACKUP LOG WITH TRUNCATE_ONLY |
None. The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model. |
Backup and restore |
BACKUP TRANSACTION |
BACKUP LOG |
Backup and restore |
BACKUP { DATABASE | LOG } WITH PASSWORD |
None. |
Backup and restore |
BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD |
None. |
Backup and Restore |
RESTORE { DATABASE | LOG } … WITH DBO_ONLY |
RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER |
Backup and restore |
RESTORE { DATABASE | LOG } WITH PASSWORD |
None. |
Backup and restore |
RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD |
None. |
Compatibility levels |
60 and 65 compatibility levels |
None. |
Compatibility levels |
70 compatibility level |
None. |
DBCC |
DBCC CONCURRENCYVIOLATION |
None. |
Extended stored procedure programming |
srv_getuserdata srv_setuserdata |
Use CLR Integration instead. |
Full-text search |
sp_fulltext_service action values clean_up, connect_timeout, and data_timeout return zero. |
None. |
Instance options |
SET REMOTE_PROC_TRANSACTIONS sp_configure'remote proc trans' |
Use linked servers and distributed queries. sp_addlinkedserver |
Remote servers |
Use of remote servers sp_addserver to create remote servers |
Use linked servers. sp_addlinkedserver to create linked servers |
Security |
sp_addalias sp_dropalias sp_addgroup sp_changegroup sp_dropgroup sp_helpgroup |
Superseded by roles |
Security |
SETUSER |
EXECUTE AS |
System tables |
syssegments |
None. |
Features Not Supported in a Future Version of SQL Server
These 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
Backup and restore
sp_helpdevice
sys.backup_devices
Collations
Hindi
Lithuanian_Classic
SQL_AltDiction_CP1253_CS_AS
None.
These collations exist in Microsoft SQL Server 2005, but are not visible through fn_helpcollations.
Compatibility level
80 compatibility level
None.
For more information about compatibility levels, see sp_dbcmptlevel (Transact-SQL).
Data types
sp_addtype
CREATE TYPE
Data types
timestamp syntax for rowversion data type
rowversion data type syntax.
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.
Database objects
CREATE DEFAULT
DROP DEFAULT
sp_bindefault
sp_unbindefault
DEFAULT keyword in CREATE/ALTER TABLE.
Database objects
CREATE RULE
DROP RULE
sp_bindrule
sp_unbindrule
CHECK keyword in CREATE/ALTER TABLE.
Database objects
sp_renamedb
MODIFY NAME in ALTER DATABASE.
Database objects
Ability to return result sets from triggers
None.
Database options
'concat null yields null' of sp_dboption
None.
Database options
sp_dboption
sp_resetstatus
ALTER DATABASE SET { ONLINE | EMERGENCY }
Database options
TORN_PAGE_DETECTION option of ALTER DATABASE
PAGE_VERIFY TORN_PAGE DETECTION option of ALTER DATABASE
DBCC
DBCC DBREINDEX
REBUILD option of ALTER INDEX.
DBCC
DBCC INDEXDEFRAG
REORGANIZE option of ALTER INDEX
DBCC
DBCC SHOWCONTIG
sys.dm_db_index_physical_stats
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 like TABLE or VIEW, or level-2 types like COLUMN or TRIGGER. For more information, see sp_addextendedproperty (Transact-SQL).
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_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.
Extended stored procedure programming
sp_addextendedproc
sp_dropextendedproc
sp_helpextendedproc
Use CLR Integration instead.
Extended stored procedures
xp_LoginConfig
IsIntegratedSecurityOnly argument of SERVERPROPERTY
Full-text search
sp_fulltext_catalog
CREATE/ALTER/DROP FULLTEXT CATALOG
Full-text search
sp_fulltext_table
sp_fulltext_column
sp_fulltext_database
CREATE/ALTER/DROP FULLTEXT INDEX
ALTER FULLTEXT INDEX
Full-text search
sp_help_fulltext_tables[_cursor]
sp_help_fulltext_columns[_cursor]
sp_help_fulltext_catalogs[_cursor]
sys.fulltext_indexes
sys.fulltext_index_columns
sys.fulltext_catalogs
Functions
fn_get_sql
sys.dm_exec_sql_text
Index options
sp_indexoption
fillfactor = 0
ALTER INDEX
fillfactor = 100
Index options
CREATE INDEX <index_option>::= syntax
CREATE INDEX <relational_index_option>::= syntax
Instance options
Default setting of disallow results from triggers option = 0
Default setting of disallow results from triggers option = 1
Locking
sp_lock
sys.syslock_information
Locking
syslockinfo
sys.syslock_information
Metadata
DATABASEPROPERTY
FILE_ID
INDEXKEY_PROPERTY
DATABASEPROPERTYEX
FILE_IDEX
sys.index_columns
Other
DB-Library
Embedded SQL for C
Although the SQL Server 2005 Database Engine still supports connections from existing applications using the DB-Library and Embedded SQL APIs, it does not include the files or documentation needed 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 modifying existing applications. Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2005 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.
Query hints
FASTFIRSTROW hint
OPTION (FAST n).
Removable databases
sp_certify_removable
sp_create_removable
sp_detach_db
Removable databases
sp_dbremove
DROP DATABASE
Security
sp_addapprole
sp_dropapprole
CREATE APPLICATION ROLE
DROP APPLICATION ROLE
Security
sp_addlogin
sp_droplogin
CREATE LOGIN
DROP LOGIN
Security
sp_adduser
sp_dropuser
CREATE USER
DROP USER
Security
sp_grantdbaccess
sp_revokedbaccess
CREATE USER
DROP USER
Security
sp_addrole
sp_droprole
CREATE ROLE
DROP ROLE
Security
sp_approlepassword
sp_password
ALTER APPLICATION ROLE
ALTER LOGIN
Security
sp_changeobjectowner
ALTER SCHEMA or ALTER AUTHORIZATION
Security
sp_defaultdb
sp_defaultlanguage
ALTER LOGIN
Security
sp_denylogin
sp_grantlogin
sp_revokelogin
ALTER LOGIN DISABLE
CREATE LOGIN
DROP LOGIN
Security
USER_ID
FILE_ID
DATABASE_PRINCIPAL_ID
FILE_IDEX
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 2005. For more information, see "Permissions of Fixed Roles" in SQL Server 2005 Books Online.
SET options
SET ANSI_NULLS
SET ANSI_PADDING
SET CONCAT_NULL_YIELDS_NULL
None.
SET options
SET OFFSETS
None.
SET options
SET ROWCOUNT for INSERT, UPDATE, and DELETE statements
TOP keyword.
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 "ms143729.note(en-US,SQL.90).gif")
Important:
The compatibility views do not expose metadata for features introduced in SQL Server 2005. It is recommended that you upgrade your applications to use catalog views. For more information, see Catalog Views (Transact-SQL).
Table hints
Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement when applied to the target table of the statement.
Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.
Textpointers
READTEXT, WRITETEXT, UPDATETEXT
None.
Textpointers
'text in row' table option
Use varchar(max), nvarchar(max), and varbinary(max) data types. For more information, see sp_tableoption (Transact-SQL).
Textpointers
TEXT, NTEXT and IMAGE data types
Use varchar(max), nvarchar(max), and varbinary(max) data types.
Textpointers
TEXTPTR(), TEXTVALID()
None.
Transact-SQL
:: function-calling sequence
Replaced by SELECT column_list FROM sys.fn_function_name()
Transact-SQL
3-part and 4-part column references in SELECT list
2-part names is the standard-compliant behavior.
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
Transact-SQL
Stored procedure numbers
sys.numbered_procedures
sys.numbered_procedure_parameters
None.
Transact-SQL
table_name.index_name syntax in DROP INDEX
index_name ON table_name syntax in DROP INDEX.
Transact-SQL
UPDATE table1, table2, ... SET syntax
Specifying more than one table in the UPDATE target is nonstandard and ambiguous.
Change History
| Release | History |
|---|---|
12 December 2006 |
|
17 July 2006 |
|