Skip to content
Home » Deprecated and discontinued features in SQL Server

Deprecated and discontinued features in SQL Server

  • by
turned off vintage black television

My co-authors and I recently wrapped up the book SQL Server 2019 Administration Inside Out, which should be hitting the shelves in the next week or two. At the end of Chapter 1 I have a small note about deprecated and discontinued features, and this post expands on that section.

The following statements are true:

  • SQL Server 2019 does not have any deprecated features, but does have discontinued features
  • SQL Server 2017 does not have any discontinued features, but does have deprecated features
  • SQL Server 2016 has both deprecated and discontinued features

Let’s discuss the difference between deprecated and discontinued features, and explain how this affects database administrators looking to move to SQL Server 2016 or newer. My current advice is to target SQL Server 2019 with at least Cumulative Update 2 (CU2).

Deprecated features

A deprecated feature is no longer under active development, and it may be removed from a future version. If we are building something new, we should avoid using that feature on the expectation that it will be gone in the future.

Discontinued features

A discontinued feature is one level up from deprecation, in that it is no longer available. If we are using a discontinued feature when we upgrade to a new version of SQL Server, that feature is gone and our code will break.

Breaking and behaviour changes

Both breaking changes and behaviour changes result in different or unexpected results after upgrading to a new version of SQL Server, potentially causing code to break. This may be the result of implicit conversions between data types being calculated differently, or a column on an existing dynamic management view (DMV) returning a different data type. Either way, these underlying changes can affect our code.

Database compatibility level

Since at least SQL Server 2016, Microsoft wants us to think about features as being linked to compatibility levels, as opposed to major version numbers. For instance, SQL Server 2019 (version 15.x) has a compatibility level of 150, and we make use of the new features introduced in 2019 by setting our database compatibility level to 150. This means that if a feature is discontinued in compatibility level 150, it will still be available in compatibility level 140 and below.

This is very helpful to keep in mind when reviewing the list of discontinued features. It’s possible on a SQL Server 2019 instance to run a database at compatibility level 100, which was the database engine used in SQL Server 2008 and SQL Server 2008 R2.

As a reminder, these are the currently supported versions of SQL Server (as at February 21, 2020), and their respective compatibility levels:

  • SQL Server 2012 (version 11.x): compatibility level 110
  • SQL Server 2014 (version 12.x): compatibility level 120
  • SQL Server 2016 (version 13.x): compatibility level 130
  • SQL Server 2017 (version 14.x): compatibility level 140
  • SQL Server 2019 (version 15.x): compatibility level 150

How does this affect me?

Compatibility levels are our friend. We might have a SQL Server 2008 R2 server that needs to be upgraded because it is no longer supported by Microsoft. We can migrate the databases on that server to a brand new SQL Server 2019 instance, and maintain compatibility with 2008 R2 by keeping the compatibility level at 100.

Even better, we can still benefit from a number of features in SQL Server 2019 including Query Store and Accelerated Database Recovery, while still maintaining compatibility with applications that expect Transact-SQL query language and other features from SQL Server 2008. Over the lifetime of that SQL Server 2019 instance we can gradually adopt newer features in our database, and increase the compatibility level until we get to 150.

Discontinued features for supported versions of SQL Server

While the official documentation makes a concerted effort to list deprecated and discontinued features as well as breaking changes, this post is my effort to show a list of features that have been discontinued for each supported compatibility level. The list of deprecated features between supported versions is much longer, so these are linked to in the following list:

Note that this list is generated from Microsoft Docs. I do not claim ownership or copyright to this content.

Level Category Discontinued feature Replacement
All Compatibility level < 100 compatibility level Databases must be set to at least compatibility level 100
All Memory Management 32-bit Address Windowing Extensions (AWE) and Hot Add memory support Use a 64-bit operating system
All Setup 32-bit installation Use a 64-bit SQL Server installation
130 ActiveX ActiveX subsystem Use command line or PowerShell scripts instead
130 Security Secure Sockets Layer (SSL) encryption Use Transport Layer Security (TLS) instead
130 Startup parameters Startup parameter -g None
120 Startup parameters Startup parameter -h None
110 Backup and Restore BACKUP { DATABASE | LOG } WITH PASSWORD None
110 Configuration options sp_configure 'user instance timeout' and 'user instances enabled' Use the Local Database feature
110 Connection protocols Support for the VIA protocol Use TCP/IP instead
110 Database objects WITH APPEND clause on triggers Re-create the whole trigger
110 Database options sp_dboption ALTER DATABASE
110 Extended Events databases_data_file_size_changed database_file_size_change
110 Extended Events databases_log_file_size_changed database_file_size_change
110 Extended Events eventdatabases_log_file_used_size_changed database_file_size_change
110 Extended Events locks_lock_timeouts_greater_than_0 lock_timeout_greater_than_0
110 Extended Events locks_lock_timeouts lock_timeout
110 Mail SQL Mail Use Database Mail
110 Programmability SQL Server Distributed Management Objects (SQL-DMO) SQL Server Management Objects (SMO)
110 Query hints FASTFIRSTROW hint OPTION (FAST n)
110 Remote servers Create new remote servers by using sp_addserver. Remote servers preserved during upgrade or created by replication can be used. Replace remote servers by using linked servers
110 Security sp_dropalias Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases.
110 Security Version parameter of PWDCOMPARE from a login earlier than SQL Server 2000 None
110 Service Broker / SMO BrokerPriority no longer implements IObjectPermission interface None
110 System tables sys.database_principal_aliases Use roles instead of aliases
110 Transact-SQL RAISERROR in the format RAISERROR integer 'string' Rewrite the statement using the current RAISERROR() syntax
110 Transact-SQL Use of *= and =* Use ANSI join syntax

Share your thoughts in the comments below.

Photo by Ajeet Mestry on Unsplash.

6 thoughts on “Deprecated and discontinued features in SQL Server”

    1. Francesco, I would follow the upgrade recommendations from Microsoft. The idea is that when you install SQL Server 2016 or higher, you turn on Query Store and run a workload to capture the plans on the old compatibility level, and then do a compatibility level update to 150. Query Store will be able to tell you what plans aren’t good, and you can force the old plans until you rewrite those bad queries. Alternatively you can stay on compatibility level 110 as long as you need for individual databases. You’re not obligated to upgrade that. That’s what the compatibility level guarantee is that Microsoft is providing.

  1. Does features apply to previous compatibility level?

    I mean, I’m on SQL Server 2019 but my databases are still on Compatibility Level 110 (SQL Server 2012). I want to rebuild a fragmented index with the options RESUMABLE = ON and OPTIMIZE_FOR_SEQUENTIAL_KEY = ON. Will these new feature apply even if the Compatibility Level is 110?

    1. Some features apply to the instance level, while some apply only to databases in the correct compatibility level. Optimize for sequential key is a 150-level feature.

  2. Hi,
    good day!
    i have a sql server 2005 which I want to upgrade to SQl 2019, a huge jump and bypassing many can I know list of deprecated/discontinued functionality between two, So I can have an idea about what would break on upgrade. before I start it.

    1. Hi Himanshu

      The good news is that you can take a backup from your 2005 database server, and restore the backup file to a new 2019 server, and it will “just work”. When it restores, the database will automatically change its compatibility level (the internal compatibility version of the database) to 100, which means it will enable the same features as SQL Server 2008 (10.0). You can then stay on that compatibility level for as long as you need. Maybe you will decide it’s good enough for the time being.

      Then over the next while, you can test with upgrading the compatibility level from 100 to 110, which is the same as SQL Server 2012 (version 11.0), and see if your application still works. Chances are you’ll only start running into issues when you go to 120, which is the same as SQL Server 2014 (version 12.0). This is because Microsoft introduced a new cardinality estimator at that version.

      The good news is, if something breaks at a higher compatibility level, you can always drop it down again. Microsoft guarantees compatibility with that level setting, on SQL Server 2016 and later, meaning that you can keep running older application software, and SQL Server will act like it’s at that older version.

      I hope that helps!

Comments are closed.