Finding Edition-Bound Features With sys.dm_db_persisted_sku_features

Before an edition move, check which persisted features the destination must support. sys.dm_db_persisted_sku_features identifies the tracked database features worth reviewing before a move. An empty result is useful evidence, but it is not a complete migration approval.

Query sys.dm_db_persisted_sku_features in the Database You Move

This view describes the current database, not every database on the instance. Begin with the database name and engine version alongside feature_name. The identifiers are administrative metadata rather than a stable application interface. Use the feature name for the review instead of assigning permanent meanings to feature_id numbers.

I record both source and intended destination editions before reading the list. A feature is a blocker only when the destination does not support what the database needs. The phrase cheaper edition does not specify a technical target. Standard and Express have different capabilities, and their limits also depend on version.

SELECT DB_NAME() AS DatabaseName,
       SERVERPROPERTY('Edition') AS SourceEdition,
       SERVERPROPERTY('ProductVersion') AS SourceVersion;
SELECT feature_name,feature_id
FROM sys.dm_db_persisted_sku_features
ORDER BY feature_name;

SQL Server 2022 and later require VIEW DATABASE PERFORMANCE STATE for this view. Earlier versions require VIEW DATABASE STATE. Coordinate those permissions through the administrative process. A permission failure is an unreviewed database, not proof that its feature list is empty.

Interpret Current Edition Support

SQL Server 2016 SP1 expanded availability of several features previously associated with Enterprise. Partitioning, compression, and columnstore are examples where historical assumptions can mislead a current review. Do not label a feature Enterprise-only from an old migration checklist.

The current source version and destination version need their own supported-feature comparison. Capacity and performance limits can differ even when both editions permit a feature. A compatible storage format does not promise an equal memory allowance, degree of parallelism, or workload performance.

The tracked list can therefore be short on a modern installation. A short list does not justify manufacturing a blocker. It is also not a reason to stop checking server-level dependencies. Keep the distinction between persisted database features and the complete operational environment visible.

Demonstrate a Tracked Feature Safely

Use a new disposable database for this exercise. Partitioning has worked in every edition since SQL Server 2016 SP1, yet the view still lists it. That makes it a safe feature to demonstrate without extra services or server permissions. Do not add a feature to a real application database merely to make the report interesting.

CREATE DATABASE EditionFeatureReviewDemo;
GO
USE EditionFeatureReviewDemo;
GO
CREATE PARTITION FUNCTION pfReviewYear(date)
AS RANGE RIGHT FOR VALUES ('20250101', '20260101');
CREATE PARTITION SCHEME psReviewYear
AS PARTITION pfReviewYear ALL TO ([PRIMARY]);
CREATE TABLE dbo.PartitionedReviewDemo
(
    OrderDate date NOT NULL,
    Amount decimal(12,2) NOT NULL
) ON psReviewYear(OrderDate);
SELECT DB_NAME() AS DatabaseName, feature_name
FROM sys.dm_db_persisted_sku_features;
GO

On my SQL Server 2025 test instance, the view returned one row, Partitioning, once the partitioned table existed. The same query in the empty database returned no rows. Your output depends on the version and on what the database already holds.

Other tracked features include change data capture, columnstore, compression, and transparent data encryption. Some of them need server-level setup or extra permissions before they appear. The purpose here is to connect a known database-level object with a tracked feature and an edition support decision.

Run sys.dm_db_persisted_sku_features Across All Databases

A database loop needs explicit coverage reporting. Store discovered features separately from checked databases. Otherwise a database with zero features is indistinguishable from one never visited. The next script quotes database identifiers and records errors rather than silently skipping them.

CREATE TABLE #SkuFeatures(DatabaseName sysname,FeatureName sysname);
CREATE TABLE #SkuCoverage(DatabaseName sysname,FeatureCount int NULL,
 ErrorMessage nvarchar(2048) NULL);
DECLARE @Database sysname,@sql nvarchar(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
 SELECT name FROM sys.databases WHERE database_id>4 AND state=0;
OPEN dbs;
FETCH NEXT FROM dbs INTO @Database;
WHILE @@FETCH_STATUS=0
BEGIN
 BEGIN TRY
  SET @sql=N'USE '+QUOTENAME(@Database)+N';
  INSERT #SkuFeatures SELECT DB_NAME(),feature_name
  FROM sys.dm_db_persisted_sku_features;
  INSERT #SkuCoverage VALUES(DB_NAME(),@@ROWCOUNT,NULL);';
  EXEC sys.sp_executesql @sql;
 END TRY
 BEGIN CATCH
  INSERT #SkuCoverage VALUES(@Database,NULL,ERROR_MESSAGE());
 END CATCH;
 FETCH NEXT FROM dbs INTO @Database;
END;
CLOSE dbs;
DEALLOCATE dbs;
SELECT * FROM #SkuCoverage ORDER BY DatabaseName;
SELECT * FROM #SkuFeatures ORDER BY DatabaseName,FeatureName;

The loop targets online user databases visible to its login. Offline databases and databases hidden by metadata permissions are outside that coverage. Capture the instance inventory separately so those omissions remain visible. Database state can also change between enumeration and execution; the catch records that failure.

Review Each Feature Against the Target

For every returned feature, identify which object or database setting uses it. Then check the current documented support of the intended destination. Do not generate automatic removal commands from feature names. Removing a persisted feature can affect data, application behavior, security, or change consumers.

I require an owner-approved replacement plan before discussing removal. Disabling CDC can break downstream readers even when the application still accepts writes. Removing encryption has a different security consequence. A migration report should make those effects understandable before anybody changes the source.

Which exact destination has been tested with a copy of this database? That question is stronger than whether one inventory query returned rows. A restore and workload test in an approved test environment provides evidence about the complete move. Keep its outcome alongside this narrower feature inventory.

Server Dependencies sys.dm_db_persisted_sku_features Cannot See

SQL Agent jobs, logins, linked-server configuration, server services, and operational scripts are not completely described by this database view. Neither are licensing obligations or processor capacity. A server feature can remain a migration dependency even when no persisted database feature is listed.

A backup from a newer SQL Server version also cannot generally be restored to an older engine version. This version direction is separate from edition support. Do not treat an empty tracked-feature list as permission to reverse that limitation.

Capacity checks deserve a workload test too. An edition supporting the feature can still impose limits relevant to database size, memory, or parallel work. A feature list describes compatibility clues, while sizing and performance need their own evidence. The view is an inventory clerk, not a migration committee.

Finish the Disposable Demonstration Cleanly

For the sample database, remove the demonstrated partitioned objects when the exercise is complete. This is different from instructing readers to remove partitioning in production. Keep any real partitioning design under the approved change process.

USE EditionFeatureReviewDemo;
DROP TABLE dbo.PartitionedReviewDemo;
DROP PARTITION SCHEME psReviewYear;
DROP PARTITION FUNCTION pfReviewYear;
SELECT feature_name FROM sys.dm_db_persisted_sku_features;
GO

In my test, the view returned no rows once the table, scheme, and function were dropped. Record the before-and-after outputs rather than claiming a result without running it. Preserve source backups and the migration decision evidence. The final approval should name the target edition and version, covered databases, unresolved errors, and completed dependency tests. That makes this useful diagnostic one concrete part of a review that another administrator can understand.

Treat sys.dm_db_persisted_sku_features as one database inventory, with explicit permission and coverage context. A successful sys.dm_db_persisted_sku_features check still leaves server dependencies and target-version testing to review.

Related reading on this blog: Upgrade Blocked: The Specified Edition Upgrade is Not Supported and Identifying Deprecated SQL Server Features with Extended Events.

A persisted feature list is not a complete edition-move approval, it is database-level evidence to compare with the exact destination.

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