As a database administrator (DBA), it is important to be aware of the edition specific features enabled in SQL Server. This knowledge can help in various tasks such as migrating databases from one server to another or determining the appropriate edition for a workload.

In a recent interview, I asked a candidate about how to check edition specific features in SQL Server. To my surprise, the candidate was not familiar with a programmatic approach but suggested interviewing developers and other DBAs to create a list. While this approach may work, it is not efficient or reliable.

Here is the answer I was expecting from the candidate:

SELECT feature_name
FROM sys.dm_db_persisted_sku_features;

Running the above query on SQL Server 2008 or later versions will provide information about any edition specific features being used. If the query returns no results, it means that no edition specific features are being utilized, making it easier to move between different versions of SQL Server.

However, if the query returns results, it indicates the usage of edition specific features such as change capture, column store indexes, compression, multiple filestream containers, in-memory OLTP features, partitioning, or Transparent Data Encryption.

Let’s consider an example. I ran the above query on the sample database “WideWorldImporters” and obtained the following resultset:

feature_name
---------------------
Change Capture
Columnstore Indexes
Compression
Multiple Filestream Containers
In-Memory OLTP
Partitioning
Transparent Data Encryption

Recently, during a Comprehensive Database Performance Health Check, I discovered that a customer was using the Enterprise Edition of SQL Server. However, upon analyzing their workload and the features they were using, it became evident that they did not require the advanced capabilities provided by the Enterprise Edition.

By running the aforementioned query, I was able to confirm that they were not utilizing any edition specific features. This prompted them to undertake a project to migrate their databases from Enterprise Edition to Standard Edition, resulting in significant cost savings in licensing fees.

Being aware of the edition specific features enabled in SQL Server can help optimize costs and ensure the appropriate edition is being utilized for a given workload. By utilizing the provided query, DBAs can easily identify if any edition specific features are being used and make informed decisions accordingly.