How to Manage SQL Server Deprecated Features

By:   |   Updated: 2024-04-05   |   Comments (2)   |   Related: > Deprecated Features


What are deprecated features in SQL Server? Is it bad to use deprecated features? How do I know if I'm using them? What should I do about it if I am using them?


This tip will answer each of these questions.

What is a SQL Server Deprecated Feature?

With each new release of SQL Server, Microsoft upgrades features and adds new features. In some cases, this means that older features or older versions of upgraded features are no longer the preferred way of doing something. If the ability to use these older versions of these features were immediately removed, client upgrades of SQL Servers would be incredibly difficult.

For instance, many years ago, the only method to reorganize or rebuild indexes was to use a DBCC REINDEX command. Microsoft added a newer, better way to defragment indexes by introducing the "ALTER INDEX…REBUILD" syntax in SQL Server 2005. Imagine having all of your maintenance plans set to use DBCC REINDEX commands and having to change them from DBCC REINDEX to ALTER INDEX at the same time as the upgrade to a new SQL Server. That would be a lot of work!

To aid in upgrades, Microsoft has chosen – in most cases – to keep both the older and newer versions of features available simultaneously rather than immediately remove the older functionality. This creates a grace period during which the feature upgrades can be completed. When this happens, the older methods are marked as deprecated. A deprecated feature still works exactly as it did before. The change to being labeled deprecated is largely cosmetic. The catch is that in a future version of SQL Server, Microsoft will remove the deprecated functionality, leaving only the newer method. Deprecation is your warning to stop using the feature before that happens.

Why Should You Care About SQL Server Deprecated Features?

There are two main reasons to care about deprecated features. The first is that there may be a much better way to complete a task. A great example of this is the DBCC SHOWCONTIG having been replaced with the more powerful and easier-to-use sys.dm_db_index_physical_stats dynamic management object (DMO). If you're still using DBCC SHOWCONTIG, it is worth the time to update your code.

The second reason relates to upgrading SQL Server. If you're still using deprecated features, you may find that the feature you're using is unavailable in a future version of SQL Server. This means there would be an extra requirement to change schema and/or code before the upgrade can be considered. Could this delay or cancel an upgrade project? That would be very frustrating, especially when you realize that SQL Server has been trying to warn you via a deprecated features DMO for the entire time you've been running the current version of SQL Server.

Is It Bad to Use SQL Server Deprecated Features?

The answer to this question depends on the circumstances. Personally, I have a few older applications in my environment that we are working to replace with brand-new products. If those applications - which I never intend to move to a newer version of SQL Server – use deprecated features, I don't think that is bad. After all, being deprecated doesn't mean the feature doesn't do what it was designed to do.

For other applications that will be around for a long time and eventually need to move to newer versions of SQL Server, having them use deprecated features is far more problematic.

How Do You Determine If Deprecated Features Are in Your Databases or Workload?

Microsoft has provided a list of deprecated features in the dm_os_performance_counters DMO. This query will return the list.

FROM sys.dm_os_performance_counters   
WHERE object_name LIKE '%SQL%Deprecated Features%';

The DMO also includes a counter that indicates if and how often the deprecated feature is being used. Adding the argument and cntr_value > 0 will reduce the list to only those features found within the databases or workload.

FROM sys.dm_os_performance_counters   
WHERE object_name LIKE '%SQL%Deprecated Features%'
and cntr_value > 0;

These counters will reset to 0 every time the SQL Server service is restarted, so be sure to check this DMO only after the service has been running for a long period of time.

How Do You Find the Actual Offending Code or Object?

This part can be harder. The query above does not tell you where or when the features were used. You need to look for them.

Some deprecated features will be found in the database definitions themselves, like 'Database Mirroring' or older compatibility levels like 'Database compatibility level 130.' These are the easiest to find. Running a query like this one for compatibility levels or simply looking at the instance in Object Explorer in SQL Server Management Studio (SSMS) will quickly show them.

SELECT [name], compatibility_level 
FROM sys.databases 
WHERE compatibility_level < 150

Some are in the database schema definitions. A query like the one below can be run against a target database to look for instances of 'Data types: text ntext or image.' The DMO from the prior section does not distinguish which database it found the feature in, so this will have to be run for each user database.

SELECT tbl.[name] TableName, col.[name] ColumnName, typ.[name] DataType 
FROM sys.tables tbl
  INNER JOIN sys.columns col ON tbl.object_id = col.object_id
  INNER JOIN sys.types typ ON col.system_type_id = typ.system_type_id
WHERE typ.[name] IN ('text','ntext','image');

Others are found in code. That code could be a mix of code internal to the databases, like that in stored procedures and functions, and ad hoc code coming directly from client applications. These items, including 'String literals as column aliases,' can be harder to find. The deprecated feature query will not tell you whether these occurrences were found within the database objects or ad hoc queries.

A profile trace, itself deprecated, or an Extended Event Session can help pinpoint where the code is being called. I started a trace and an Extended Event Session for this demo before running a deprecated query.

These are the trace event classes:

This is a screenshot from the SQL Profiler application showing 2 deprecated feature event classes.

Here is a sample from when I ran a query that uses a string literal as a column alias:

This is the trace output in the SQL Profiler application.

Here are the same event classes on the Extended Event Wizard in SSMS:

This is a screenshot of the Extended Event create session wizard in SSMS.  I had typed the word "deprecation" into the filter to limit to only those 2 events.

Notice that the Extended Event session warned us about the running trace before getting to the offending query.

While watching the live data output of the session, it quickly warned me about the running trace that is part of the deprecated profiler application.

How Do I Fix Deprecated Feature Usage?

Some of the fixes will be more apparent than others. If the deprecated feature is an older compatibility level, choose a newer one. For any that are not immediately apparent, I've found that Microsoft's Learn website, formerly Books on Line, tends to spell out the corrective action when you visit the page for the deprecated feature. This screenshot from the learn Microsoft page for the text and ntext data types says to use an alternative data type in their place.

Screenshot from learn.Microsoft page for the text and ntext data types

Final Thoughts

I often look for deprecated feature usage in development environments. If my development team is writing new or upgrading existing code there and I see that they are using deprecated features, I can use that as a coaching opportunity to block deprecated code from ever going to production.

Even if the deprecated feature usage is not part of new development, what better time to address it than during a build cycle?

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-04-05

Comments For This Article

Tuesday, April 9, 2024 - 6:02:55 PM - Tim Back To Top (92166)
One interesting, deprecated feature I saw at is "Not ending Transact-SQL statements with a semicolon". I could not find a MS site with that newer than SQL Server 2017. If discontinued, it would require a lot of changes.

Tuesday, April 9, 2024 - 9:59:22 AM - alzdba Back To Top (92162)
You've forgotten to relay to the most recent and complete to monitor for deprecated features usage: Extended Events
e.g. MSSQLTips article "Identify Deprecated SQL Server Code with Extended Events"

get free sql tips
agree to terms