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

Question: How do you check which edition-specific features a SQL Server database uses?

Answer: Start with sys.dm_db_persisted_sku_features in the database you want to move. It identifies certain features persisted in the database files. It is a useful first check when moving between editions, but it does not certify that the whole migration will succeed.

I asked this in an interview for a DBA role involving database migrations. The candidate said they had handled upgrades and downgrades before. When I asked how they would identify edition-specific features, their answer was to interview the developers and DBAs and build a list.

Talking to the team helps establish how an application uses the server. For this particular question, though, I also wanted the candidate to know the database could give us a starting point:

USE WideWorldImporters;
GO
SELECT feature_name
FROM sys.dm_db_persisted_sku_features;
GO

A returned feature is something to investigate against the target edition’s feature matrix. For example, the original result lists compression, partitioning, columnstore and In-Memory OLTP. Several features that once required Enterprise became available more widely from SQL Server 2016 SP1, so an old list is not a current licensing decision.

No rows means this view found no features it reports as edition-restricted. It does not check every server-level dependency, capacity limit, application requirement or licensing condition. It also does not permit restoring a newer-version database backup onto an older SQL Server engine. Check every database, the target version’s support matrix and the application before approving a move.

On SQL Server 2019 and earlier, this view requires VIEW DATABASE STATE. From SQL Server 2022, it requires VIEW DATABASE PERFORMANCE STATE.

During a Comprehensive Database Performance Health Check, I found a customer paying for Enterprise Edition whose workload and features appeared suitable for Standard. This query helped start that discussion. They pursued the migration and saved hundreds of thousands of dollars in licensing costs. The useful interview answer is to know where to start, then understand what the result does and does not prove.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.