How to Check Edition Specific Features Enabled In SQL Server? – Interview Question of the Week #180

Question: How to Check Edition Specific Features Enabled In SQL Server?

Answer: I had asked this question in the recent interview of the person who was applying for the job of the DBA. The very task the DBA supposed to get was of migrating databases from one server to another server. Additionally, he was also claimed that in the past he has done many upgrades as well as downgrades of the SQL Server.

To my surprise when I asked him this question, he was like, he has no idea how to do the same programmatically but he can go to all the developers and DBAs and interview them for this and can create the list. Honestly, that is for sure not a great idea.

Honestly, here is the answer which I was expecting from him. Any users who are running SQL Server 2008 or later versions of SQL Server can run following script to learn about if they are using any edition specific features.

SELECT feature_name
FROM sys.dm_db_persisted_sku_features;
GO 

If above query returns no results that means you are not using any feature which is edition specific and you can easily move from one version to another version.

If you are using change capture, column store indexes, compression, multiple filestream containers, in-memory OLTP features, partitioning or Transparent Data Encryption – the query above will return the results.

I ran above query for the sample database WideWorldImporters and I got following resultset.

Recently, during my recent Comprehensive Database Performance Health Check, I realize that my customer is using Enterprise Edition but in the reality, it looked like they did not need to spend that much money for the little workload and the features which they were using. I quickly ran above query and explained to them they are not using any edition specific features. They immediately undertook the project of moving their databases from Enterprise Edition to Standard Edition and it saved hundreds of thousands of dollars in the licensing cost.

Reference: Pinal Dave (https://blog.sqlauthority.com)