I have a production database running on SQL Server 2016 Enterprise. I ran the below query to check the edition-specific feature and see the compression feature in use. Can we still downgrade the edition?


SELECT feature_name
FROM sys.dm_db_persisted_sku_features;





1 Answer 1


From Microsoft Documentation

Feature Enterprise Standard
Web Express Express
Columnstore 1, 2 Yes Yes Yes Yes Yes
In-Memory OLTP 1, 2 Yes Yes Yes Yes 3 Yes
Stretch Database Yes Yes Yes Yes Yes
Persistent Main Memory Yes Yes Yes Yes Yes
Multi-instance support 50 50 50 50 50
Table and index partitioning 2 Yes Yes Yes Yes Yes
Data compression 2 Yes Yes Yes Yes Yes
Resource Governor Yes No No No No
Partitioned Table Parallelism 2 Yes Yes Yes No No
Multiple Filestream containers 2 Yes Yes Yes Yes Yes
NUMA Aware Large Page Memory and Buffer Array Allocation Yes No 4 No No No
Buffer Pool Extension Yes Yes No No No
I/O Resource Governance Yes No No No No
Read-Ahead Yes No No No No
Advanced Scanning Yes No No No No
Delayed Durability Yes Yes Yes Yes Yes

1 - In-Memory OLTP data size and Columnstore segment cache are limited to the amount of memory specified by edition in the Scale Limits section. The max degree of parallelism is limited. The degree of process parallelism (DOP) for an index build is limited to 2 DOP for the Standard Edition and 1 DOP for the Web and Express Editions. This refers to columnstore indexes created over disk-based tables and memory-optimized tables.

2 - Applies to SQL Server 2016 (13.x) SP1 as part of creating a Common Programmability Surface Area (CPSA) across editions. Aggregate Pushdown, String Predicate Pushdown, and SIMD Optimizations are SQL Server Enterprise Edition scalability enhancements. For more detail, see Columnstore indexes - what's new.

3 - This feature isn't included in the LocalDB installation option.

4 - Standard Edition and SQL Server + CAL-based licensing can restrict how many processors SQL Server Standard can use, but SQL Server Standard is NUMA aware.

As shown in the table above, you can downgrade to Standard Edition since the Data Compression feature is supported in all five editions of SQL Server 2016 (starting with Service Pack 1).

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.