Deprecated Database Engine Features in SQL Server 2008

This topic describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2008. 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.

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.

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.

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).

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.

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.

Reference

Corrected the information in the Linked servers category. Replaced OLEDB as the deprecated feature for linked servers with the SQLOLEDB provider and added SQL Server Native Client as the replacement feature.