deprecated-database-engine-features-in-sql-server-2017.md

title	Deprecated Database Engine Features | Microsoft Docs
titleSuffix	SQL Server 2019
description	Find out about deprecated database engine features that are still available in SQL Server 2017 (14.x), but shouldn't be used in new applications.
ms.custom	seo-lt-2019
ms.date	03/30/2020
ms.prod	sql
ms.prod_service	high-availability
ms.reviewer
ms.technology	release-landing
ms.topic	conceptual
helpviewer_keywords	deprecated features [SQL Server] Database Engine [SQL Server], backward compatibility deprecation [SQL Server], feature list
ms.assetid
author	MikeRayMSFT
ms.author	mikeray
monikerRange	>=sql-server-2017||=sqlallproducts-allversions||>=sql-server-linux-2017

Deprecated Database Engine Features in SQL Server 2017

[!INCLUDEtsql-appliesto-ss2017-xxxx-xxxx-xxx-md]

This topic describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2017 (14.x). Deprecated features should not be used in new applications.

When a feature is marked deprecated, it means:

• The feature is in maintenance mode only. No new changes will be done, including those related to inter-operability with new features.

• We strive not to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we may choose to permanently remove the feature from SQL Server if it limits future innovations.

• For new development work, we do not recommend using deprecated features.

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 Use SQL Server Objects.

The value of these counters are also available by executing the following statement:

SELECT * FROM sys.dm_os_performance_counter
WHERE object_name = 'SQLServer:Deprecated Features';

Note

This list is identical to the [!INCLUDEsssql15-md] list. There are no new deprecated or discontinued Database Engine features announced for [!INCLUDEsssqlv14-md].

Features deprecated in the next version of SQL Server

The following SQL Server Database Engine features will be deprecated 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.

Back up and Restore

Deprecated feature	Replacement	Feature name
RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD continues to be deprecated. BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are discontinued.	None.	BACKUP DATABASE or LOG WITH PASSWORD BACKUP DATABASE or LOG WITH MEDIAPASSWORD

Compatibility levels

Deprecated feature	Replacement	Feature name
Upgrade from version 100 (SQL Server 2008 and SQL Server 2008 R2).	When a SQL Server version goes out of support, the associated Database Compatibility Level will be marked deprecated. However, we will continue to support applications certified on any supported database compatibility level as long as possible, to make the upgrades easier. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).	Database compatibility level 100

Database objects

Deprecated feature	Replacement	Feature name
Ability to return result sets from triggers	None	Returning results from trigger

Encryption

Deprecated feature	Replacement	Feature name
Encryption using RC4 or RC4_128 is deprecated and is scheduled to be removed in the next version. Decrypting RC4 and RC4_128 is not deprecated.	Use another encryption algorithm such as AES.	Deprecated encryption algorithm

Deprecated feature	Replacement	Feature name
Hash algorithms	Using the MD2, MD4, MD5, SHA, and SHA1 is deprecated.	Use SHA2_256 or SHA2_512 instead. Older algorithms will continue working, but they will raise a deprecation event.

Remote servers

Deprecated feature	Replacement	Feature name
Remote servers	sp_addremotelogin sp_addserver sp_dropremotelogin sp_helpremotelogin sp_remoteoption	Replace remote servers by using linked servers. sp_addserver can only be used with the local option.
Remote servers	@@remserver	Replace remote servers by using linked servers.
Remote servers	SET REMOTE_PROC_TRANSACTIONS	Replace remote servers by using linked servers.

Set options

Deprecated feature	Replacement	Feature name
Set options	SET ROWCOUNT for INSERT, UPDATE, and DELETE statements	TOP keyword

Table hints

Category	Deprecated feature	Replacement	Feature name
HOLDLOCK table hint without parenthesis.	Use HOLDLOCK with parenthesis.	HOLDLOCK table hint without parenthesis

Features deprecated 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 deprecated in a later version. The specific version of SQL Server has not been determined.

Compatibility levels

Deprecated feature	Replacement	Feature name
sp_dbcmptlevel	ALTER DATABASE ... SET COMPATIBILITY_LEVEL. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).	sp_dbcmptlevel
Database compatibility level 110 and 120.	Plan to upgrade the database and application for a future release. However, we will continue to support applications certified on any supported database compatibility level as long as possible, to make the upgrades easier. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).	Database compatibility level 110 Database compatibility level 120

XML

Deprecated feature	Replacement	Feature name
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

Backup and restore

Deprecated feature	Replacement	Feature name
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
sp_addumpdevice 'tape'	sp_addumpdevice 'disk'	ADDING TAPE DEVICE
sp_helpdevice	sys.backup_devices	sp_helpdevice

Collations

Deprecated feature	Replacement	Feature name
Korean_Wansung_Unicode Lithuanian_Classic SQL_AltDiction_CP1253_CS_AS	None. These collations exist in SQL Server 2005 (9.x), but are not visible through fn_helpcollations.	Korean_Wansung_Unicode Lithuanian_Classic SQL_AltDiction_CP1253_CS_AS
Hindi Macedonian	These collations exist in SQL Server 2005 (9.x) and higher, but are not visible through fn_helpcollations. Use Macedonian_FYROM_90 and Indic_General_90 instead.	Hindi Macedonian
Azeri_Latin_90 Azeri_Cyrilllic_90	Azeri_Latin_100 Azeri_Cyrilllic_100	Azeri_Latin_90 Azeri_Cyrilllic_90

Configuration

Deprecated feature	Replacement	Feature name
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

Note

The cookie OUTPUT parameter for sp_setapprole is currently documented as varbinary(8000) which is the correct maximum length. However the current implementation returns varbinary(50). If developers have allocated varbinary(50) the application might require changes if the cookie return size increases in a future release. Though not a deprecation issue this is mentioned in this topic because the application adjustments are similar. For more information, see sp_setapprole (Transact-SQL).

See Also

Discontinued Database Engine Functionality in SQL Server 2016