liveBook · Manning

Sys.dm_db_persisted_sku_features

As explained in 100 SQL Server Mistakes and How to Avoid Them

sys.dm_db_persisted_sku_features is a SQL Server dynamic management view that reports edition-specific features persisted in a database.

Mistake: choosing Enterprise edition by default. The expense and migration risk are avoidable when a database’s persisted edition-specific features can be checked directly. section 8.5

Partitioning reveals the dependency

A database using table partitioning returns one row, identifying partitioning as a persisted Enterprise-only feature. A database using none returns an empty result set. section 8.5

Listing 8.4 Discovering Enterprise-only features
USE EnterpriseDatabase ;
GO

SELECT feature_name 
FROM sys.dm_db_persisted_sku_features ;

Run the DMV query in the target database to list persisted Enterprise-only features. section 8.5

Apply this check before selecting an edition:

  • Use Standard edition unless the query identifies a required Enterprise-only feature; use Developer edition for fully featured development environments, not production. section 8.5

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Defines the DMV and demonstrates its result, but stops short of cataloging every reported feature.
Examples
Uses a partitioned database and a SELECT query returning feature_name.
What it assumes
Assumes basic SQL Server database querying and edition awareness.
What you can do after
Diagnose persisted Enterprise-only dependencies before choosing Standard or Enterprise edition.
How it connects
Connects dynamic management views with SQL Server editions and table partitioning.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.