How to List the Deprecated Features in a SQL Server Instance using T-SQL

Posted on by Ian

In SQL Server, you can use the sys.dm_os_performance_counters system dynamic management view to return a list of the features designated as deprecated in the current instance of SQL Server.

To return just the deprecated features, filter the view to just the SQLServer:Deprecated Features object.

The view also includes a counter that lists the number of times each deprecated feature was encountered since SQL Server last started. This can be helpful for identifying whether or not your application is still using any deprecated features.

Running this on SQL Server requires VIEW SERVER STATE permission.

Example 1 – Basic Usage

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

When I run that on my SQL Server 2019 instance, I get 254 rows, so I won’t list them here. On my SQL Server 2017 instance, I get 253 rows.

If I narrow the results down to just two specific rows, I might get something like this:

SELECT *
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Deprecated Features'
AND instance_name LIKE '%timestamp%';

Result:

+-------------------------------+----------------+------------------------------------+--------------+-------------+
| object_name                   | counter_name   | instance_name                      | cntr_value   | cntr_type   |
|-------------------------------+----------------+------------------------------------+--------------+-------------|
| SQLServer:Deprecated Features | Usage          | INSERT NULL into TIMESTAMP columns | 0            | 65792       |
| SQLServer:Deprecated Features | Usage          | TIMESTAMP                          | 1            | 65792       |
+-------------------------------+----------------+------------------------------------+--------------+-------------+

The Microsoft website has a full list of deprecated database engine features in SQL Server 2017, along with recommended action you should take for any feature marked for deprecation. The list is the same as the one for SQL Server 2016.

Example 2 – Using the View via an Application

If you’re using the sys.dm_os_performance_counters view in an application, you should avoid using the asterisk wildcard (*) to return all columns from system dynamic management views and functions (as advised by Microsoft). In such cases, if you want to return all columns, it’s better to include each column name within the query.

Therefore, we could change the previous example to the following:

SELECT
  object_name, 
  counter_name, 
  instance_name, 
  cntr_value, 
  cntr_type
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Deprecated Features';

Microsoft advises that Azure SQL Data Warehouse and Parallel Data Warehouse return an extra column, called pdw_node_id, so that’s something to bear in mind too.

Example 3 – Padding

The first three columns use a data type of nchar(128). This may cause a lot of whitespace to be padded to the right of the text.

To eliminate this extra whitespace, you can use the RTRIM() function (or the TRIM() function if you prefer) on the first three columns:

SELECT
  RTRIM(object_name) AS Object, 
  RTRIM(counter_name) AS Counter, 
  RTRIM(instance_name) AS Instance, 
  cntr_value, 
  cntr_type
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Deprecated Features';

Microsoft Documentation

Here are links to relevant documentation on the Microsoft website: