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?

How to Check Edition Specific Features Enabled In SQL Server? - Interview Question of the Week #180 persistedsku1-800x268

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 

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

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)

, , ,
Previous Post
How to Find Service Account for SQL Server and SQL Server Agent? – Interview Question of the Week #179
Next Post
How to Generate Random Password in SQL Server? – Interview Question of the Week #181

Related Posts

3 Comments. Leave new

  • nakulvachhrajani
    July 1, 2018 4:55 pm

    This is a great tip! Thank-you very much for sharing!

    Reply
  • Online indexing would not show up in that query ? If so ,if they need maybe would be problematc ?

    Reply
  • Hi Pinal, can you help us how to we can check enterprise feautures check in sql server 2005 , for edition downgrade we required this one.

    Reply

Leave a Reply

Menu