Can You Actually Downgrade to Standard Edition? Ask the Database First

Every few years the licensing conversation comes back around. Enterprise edition is a substantial line item, someone in finance notices, and the question lands on your desk: “Can we move this database to Standard?” The optimistic answer is “probably.” The honest answer starts with a single dynamic management view, because some features do not just change behaviour, they change the way the data is physically stored. Historically, once one of those features was in a database, the files carried a flag that said “Enterprise or Developer only,” and a lower edition would refuse to restore or attach the database at all.

The view that surfaces those flags is sys.dm_db_persisted_sku_features.^[1] It is small, it is fast, and it lists the storage features in a database that were historically Enterprise-only. The catch, and the reason this post exists, is that on modern SQL Server the view over-reports: most of what it flags no longer blocks anything, and reading it as a literal pass-or-fail gate will send you off removing features you never needed to remove. This post covers what it reports, why the answer changed at SQL Server 2016 SP1, the trap that makes people read it wrong, and a script to sweep every database on an instance at once.

What the view actually tells you

Some Database Engine features change the physical layout of the database files. Data compression rewrites how rows and pages are stored. A columnstore index is an entirely different storage structure. Transparent Data Encryption encrypts the pages at rest. Because the bytes on disk are different, a database using one of these features can only be restored or attached on an edition that understands that layout. The whole point of the view is to inventory those features so you know what a target edition would have to support.

sys.dm_db_persisted_sku_features lists those features for the current database. Run it in a database and you get one row per feature in use:

If the view returns no rows, the database carries none of these storage features, and nothing in its physical format will stop it from being restored on any edition. That is the result you are hoping for, and it is an unambiguous green light. Rows are where it gets interesting, because a row does not automatically mean “blocked.” More on that below.

Two details worth knowing before you build anything on top of it. First, key your logic off feature_name, not feature_id. Microsoft documents the id as informational only, not supported, with no guarantee of future stability. The name is the contract. Second, the permission required changed with SQL Server 2022: earlier versions want VIEW DATABASE STATE, while 2022 and later want VIEW DATABASE PERFORMANCE STATE.^[1] If a monitoring account suddenly stops seeing rows after an upgrade, that permission change is the first thing to check.

The seven features it can report

Across modern versions, the view can report these feature_name values. I have listed the removal path for each, but as the next section explains, on current versions you rarely need to remove any of them except TDE:

• ChangeCapture – the database has Change Data Capture enabled. Remove it with sys.sp_cdc_disable_db.
• ColumnStoreIndex – at least one table has a columnstore index. Remove it with DROP INDEX or ALTER INDEX.
• Compression – at least one table or index uses data compression or the vardecimal storage format. Remove it with ALTER TABLE / ALTER INDEX ... REBUILD WITH (DATA_COMPRESSION = NONE), or sp_tableoption for vardecimal.
• InMemoryOLTP – the database has a MEMORY_OPTIMIZED_DATA filegroup. This one is stubborn: you cannot drop a memory-optimized filegroup, so removing it means moving the data to a new database.
• MultipleFSContainers – a FILESTREAM filegroup with more than one container.
• Partitioning – partitioned tables, indexes, schemes, or functions. Collapsing a table to a single partition is not enough; you must remove the partitioned object, moving data out with SWITCH PARTITION first if it holds rows.
• TransparentDataEncryption – the database is encrypted with TDE. Remove it with ALTER DATABASE ... SET ENCRYPTION OFF and then drop the database encryption key once decryption completes.

That is the full menu. If your database returns nothing from this list, the physical format is portable.

The turning point: SQL Server 2016 SP1

Here is the part that trips up anyone working from older memory or older blog posts. Before SQL Server 2016 Service Pack 1, every feature in that list was Enterprise and Developer only. Using data compression, partitioning, or a columnstore index on a database meant that database was locked to Enterprise, full stop.

SQL Server 2016 SP1 changed the deal. As part of building a Common Programmability Surface Area across editions, Microsoft made six of the seven features available in Standard, Web, and Express.^[2] Compression, columnstore, partitioning, In-Memory OLTP, Change Data Capture, and multiple FILESTREAM containers are no longer edition-locked. A database that uses data compression on SQL Server 2019 Standard is perfectly happy, and a compressed database restores onto Standard without complaint.

Here is the twist, and it is the whole reason this post exists: the view did not get the memo. sys.dm_db_persisted_sku_features still reports those six features even though they no longer restrict anything. I ran the whole-instance sweep from the end of this post against live SQL Server 2019 and 2022 instances, and both happily listed Compression, Partitioning, and ColumnStoreIndex for databases that are using those features right now. Every one of those databases would restore onto the matching Standard edition without touching a thing. The view is reporting historical Enterprise heritage, not a current restriction. So on any build from 2016 SP1 onward, a row for one of those six is informational, not a blocker.

The lone genuine holdout is Transparent Data Encryption. TDE stayed edition-restricted, and its availability has crept down the edition ladder over time rather than opening up all at once:

TDE arrived in Standard edition with SQL Server 2019.^[3] On 2016 and 2017 it is Enterprise only. Web and Express have never supported it, and the Web edition itself was discontinued in SQL Server 2025, which is why that column reads “n/a” on the last row.

The trap: a row is not a verdict

This is the single most important thing to understand about the view, and it is easy to miss. sys.dm_db_persisted_sku_features reports features that were historically edition-restricted, and it reports features that are not supported on every current edition. It does not know, and does not care, which specific edition you are trying to move to, or that most of what it lists stopped being a restriction years ago. A row is an item to check, not a verdict.

The correct way to read the output is one feature at a time: for each row, ask whether the edition and version you are actually moving to supports that feature. On modern versions that check collapses to a very short list. The six non-TDE features pass everywhere from 2016 SP1 on, so they never block a move. That leaves TDE.

Walk through what that means for TDE on SQL Server 2019 or later. TDE is supported on Enterprise and Standard, but not on Web or Express. So the moment you enable TDE, the view reports a TransparentDataEncryption row, even on Enterprise, because the database can no longer move to Web or Express. But if your actual target is Standard, that row is not a blocker at all, since Standard supports TDE. The view is telling you “this cannot go to the lowest edition,” while your real question was “can this go to Standard.”

So read the output against your specific target, not as a simple pass or fail:

• A row for Compression, ColumnStoreIndex, Partitioning, InMemoryOLTP, ChangeCapture, or MultipleFSContainers on a build from 2016 SP1 onward does not block anything. Those features exist on every edition, so the database restores as-is. Note the feature for your records, then move on.
• A TransparentDataEncryption row is the one that can actually stop you. It blocks Web and Express on every version. It blocks Standard only on 2016 and 2017. On 2019 and later, TDE is fine for a move to Standard.

One more source of surprises: Developer edition has the full Enterprise feature set. A database built and tested on a developer’s Developer-edition instance can quietly pick up Enterprise-only storage features, then fail to deploy to a Standard production server. Running this view as part of a pre-deployment check catches that before it becomes an outage.

What the view does not catch

It is tempting to treat a clean result as a green light for the whole migration. It is not. This view answers a narrow question about physical file portability, which is whether the database can be restored or attached at all. It says nothing about whether your workload will run the same way once it lands.

Plenty of edition differences never touch the file format and so never appear here. Resource Governor, online index rebuilds, parallel index operations, Always On availability groups, buffer pool extension, and the memory and core ceilings on Standard are all real constraints that this view is blind to. A database can pass the persisted-feature check cleanly and still run into a wall because the workload depends on an Enterprise-only runtime feature, or because Standard’s memory limit throttles it. Treat “can I downgrade to Standard” as two questions: does the database restore (this view), and does the workload fit inside Standard’s limits (a separate review of what the application actually uses).

Sweeping every database on the instance

Running the view one database at a time is fine for a spot check, but before a migration you want the whole instance in one result set. The view is database-scoped, so a sweep has to visit each database in turn. A cursor over sys.databases with a small piece of dynamic SQL does the job, and wrapping each database in its own TRY...CATCH keeps one inaccessible database (an Always On secondary that is not readable, for example) from stopping the whole scan:

A few deliberate choices in there. The database name goes into the query two different ways on purpose: as an identifier via QUOTENAME when it names the catalog to read from, and as a parameter (@db) when it is just a string value in the result. That keeps a database with an awkward name, or a genuinely hostile one, from breaking or subverting the dynamic SQL. The cursor uses the LOCAL FORWARD_ONLY STATIC READ_ONLY form with the @@CURSOR_ROWS countdown, so there is no priming fetch and no @@FETCH_STATUS dance. I wrote about that cursor pattern in more detail in a better cursor pattern for SQL Server.

The result is a tidy list of every persisted feature across the instance. Here is a representative result, with the database names genericized, from a SQL Server 2022 instance:

Every one of those databases is happily using its feature on a 2022 instance, and every one would restore onto Standard 2022 without a single change, because compression, partitioning, and columnstore are cross-edition from 2016 SP1 on. This is the over-reporting in action: the view lists Enterprise-heritage features, not present-day blockers. I saw the same behaviour on a 2019 instance; a 2025 instance that happened to use none of these features returned no rows at all, which is exactly what a clean bill of health looks like. If a TransparentDataEncryption row had shown up instead, that would be the one to stop and think about, and even then only if the target were Web, Express, or a pre-2019 Standard. (The feature_id values are informational only; key any logic off feature_name.)

If your instance is entirely read-accessible and you would rather avoid the cursor, you can build a single UNION ALL batch across the databases with STRING_AGG and run it once. It is tidier, but less forgiving, since one unreadable database fails the whole batch rather than being skipped. I walked through that trade-off, turning a per-row loop into one set-based statement, in eliminating cursors with set-based alternatives.

Putting it to work

The next time the licensing question comes around, you do not have to guess. Run the sweep, read each row against the specific edition and version you are targeting, and you will know in seconds which databases are genuinely portable and which are carrying a TDE flag that may or may not matter for your target. On any build from 2016 SP1 on, most of the rows you see are just heritage and need no action at all. Then move on to the harder half of the question, whether the workload itself fits inside the edition you are moving to. If you also manage compatibility levels across a fleet, the same whole-instance approach shows up in upgrading database compatibility levels across a whole instance.

Have you ever been surprised by one of these flags mid-migration, or found a feature you did not know was in use? I would like to hear the story. Find me on Bluesky or LinkedIn.

References

1. sys.dm_db_persisted_sku_features (Transact-SQL) – Microsoft Learn. The feature list, the note that feature_id is informational only, and the permission change in SQL Server 2022. ↩
2. Editions and supported features of SQL Server 2016 – Microsoft Learn. The SP1 Common Programmability Surface Area footnotes that moved six features across editions. ↩
3. Editions and supported features of SQL Server 2022 – Microsoft Learn. The Security table showing TDE on Enterprise and Standard, but not Web or Express. ↩