Problem
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?
Solution
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 MSSQLTips.com SELECT * 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 MSSQLTips.com SELECT * 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.
--From MSSQLTips.com 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.
--From MSSQLTips.com
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:
Here is a sample from when I ran a query that uses a string literal as a column alias:
Here are the same event classes on the Extended Event Wizard in SSMS:
Notice that the Extended Event session warned us about the running trace before getting to the offending query.
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.
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
