I have SQL Server 2016 Enterprise edition, I restored the DB's to SQL Server 2019 Enterprise edition. But I want to check if I can restore it in SQL Server 2019 Standard.

if I run the DMV sys.dm_db_persisted_sku_features in SQL Server 2019 Enterprise, the results that returns: Compression, Partitioning, ColumnStoreIndex

which means I can't downgrade to standard edition. but according to Microsoft documentation SQL Server 2019 Standard supports the these features. so why these results appears in sys.dm_db_persisted_sku_features?


1 Answer 1


The sys.dm_db_persisted_sku_features DMV tells you which features are enabled in the database (without having to query each separately). Whether it actually makes a difference to moving editions is dependent on which target versions you're moving the database to. Certainly it could be made more useful, but it's not useless. For example, TDE and CDC are still not supported on Express or LocalDB.

There is a note in the documentation that clearly states:

Starting with SQL Server 2016 (13.x) Service Pack 1, these features, except TransparentDataEncryption are available across multiple SQL Server Editions, and not limited to Enterprise or Developer Editions only.

So the code you want is:

select *
from sys.dm_db_persisted_sku_features
where feature_name = 'TransparentDataEncryption'

I think the docs are actually wrong still, because Change Data Capture is not available in Express Edition or LocalDB. The DMV could certainly be made more useful by adding columns showing which edition supports these features, at least for the current version.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.