Does your Application use deprecated features of SQL Server or SQL Azure?

In computer science, the term deprecated is used to indicate that a feature available in the current version of the software will be eliminated in the next versions. It is not recommended to use deprecated features in new projects and it is recommended to adapt existing software that uses deprecated features!

Even on SQL Server and Azure SQL Database some features are deprecated, Microsoft therefore does not recommend their use for new developments.

The list of deprecated features in SQL Server 2017 is available at this link (SQL Server 2019 does not deprecate any features).

How is it possible to identify the database deprecated features that are, at the same time, used by an Application?

We can answer this question using the DMV sys.dm_os_performance_counters which returns a row for each performance counter managed by SQL Server instance. By filtering the rows returned by the sys.dm_os_performance_counters on the object_name and asking for the string “:Deprecated Featureswe obtain the list of deprecated features in the version of SQL Server or Azure SQL Database you are using.

We know the system view sys.sysdatabases is deprecated, so the following script will produce an increment of the related counter.

SELECT
[object_name]
,[instance_name]
,[cntr_value]
FROM
sys.dm_os_performance_counters
WHERE
([object_name] LIKE N'%:Deprecated Features%')
AND ([instance_name] = 'sysdatabases');
SELECT
*
FROM
sys.sysdatabases
OPTION (RECOMPILE);
SELECT
[object_name]
,[instance_name]
,[cntr_value]
FROM
sys.dm_os_performance_counters
WHERE
([object_name] LIKE N'%:Deprecated Features%')
AND ([instance_name] = 'sysdatabases');
GO

The query on sys.dm_os_performance_counters was performed twice, before and after the deprecated call to the sys.sysdatabases. The value of the counter :Deprecated Featuressysdatabases” is incremented by one for each execution.

The following query returns all deprecated counters values from the last restart of SQL Server service.

SELECT
[object_name]
,[instance_name]
,[cntr_value]
FROM
sys.dm_os_performance_counters
WHERE
([object_name] LIKE N'%:Deprecated Features%');
GO

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sergio Govoni

Sergio Govoni

CTO at Centro Software, Microsoft Data Platform MVP