Skip to main content
Editions and Supported Features for SQL Server 2016
 

Updated: December 1, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This topic provides details of features supported by the different editions of SQL Server 2016.

The SQL Server Evaluation edition is available for a 180-day trial period.

For the latest release notes and what's new information, see the following:

Try SQL Server 2016!

Download from Evaluation Center  Download SQL Server 2016 from the Evaluation Center

Azure Virtual Machine small  Spin up a Virtual Machine with SQL Server 2016 already installed

For features supported by Evaluation and Developer editions see SQL Server Enterprise Edition.

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Maximum compute capacity used by a single instance - SQL Server Database Engine1Operating system maximumLimited to lesser of 4 sockets or 24 coresLimited to lesser of 4 sockets or 16 coresLimited to lesser of 1 socket or 4 coresLimited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance - Analysis Services or Reporting ServicesOperating system maximumLimited to lesser of 4 sockets or 24 coresLimited to lesser of 4 sockets or 16 coresLimited to lesser of 1 socket or 4 coresLimited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool per instance of SQL Server Database EngineOperating System Maximum128 GB64 GB1410 MB1410 MB
Maximum memory for Columnstore segment cache per instance of SQL Server Database EngineUnlimited memory32 GB216 GB2352 MB2352 MB2
Maximum memory-optimized data size per database in SQL Server Database EngineUnlimited memory32 GB216 GB2352 MB2352 MB2
Maximum memory utilized per instance of Analysis ServicesOperating System MaximumTabular: 16 GB

MOLAP: 64 GB
N/AN/AN/A
Maximum memory utilized per instance of Reporting ServicesOperating System Maximum64 GB64 GB4 GBN/A
Maximum relational database size524 PB524 PB524 PB10 GB10 GB
  1. Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model. For more information, see Compute Capacity Limits by Edition of SQL Server.

2 Applies to SQL Server 2016 SP1.

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Server core support 1YesYesYesYesYes
Log shippingYesYesYesNoNo
Database mirroringYesYes

Full safety only
Witness onlyWitness onlyWitness only
Backup compressionYesYesNoNoNo
Database snapshotYesYes 3Yes 3Yes 3Yes 3
Always On failover cluster instancesYes

Number of nodes is the operating system maximum
Yes

Support for 2 nodes
NoNoNo
Always On availability groupsYes

Up to 8 secondary replicas, including 2 synchronous secondary replicas
NoNoNoNo
Basic availability groups 2NoYes

Support for 2 nodes
NoNoNo
Connection directorYesNoNoNoNo
Online page and file restoreYesNoNoNoNo
Online indexingYesNoNoNoNo
Online schema changeYesNoNoNoNo
Fast recoveryYesNoNoNoNo
Mirrored backupsYesNoNoNoNo
Hot add memory and CPUYesNoNoNoNo
Database recovery advisorYesYesYesYesYes
Encrypted backupYesYesNoNoNo
Hybrid backup to Windows Azure (backup to URL)YesYesNoNoNo

1 For more information on installing SQL Server 2016 on Server Core, see Install SQL Server 2016 on Server Core.

2 For more information about Basic availability groups, see Basic Availability Groups.

3 Applies to SQL Server 2016 SP1.

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Columnstore 1YesYes 2Yes 2Yes2Yes2
In-Memory OLTP 1YesYes 2Yes 2Yes 2, 3Yes 2
Stretch DatabaseYesYesYesYesYes
Persistent Main MemoryYesYesYesYesYes
Multi-instance support5050505050
Table and index partitioningYesYes 2Yes 2Yes 2Yes 2
Data compressionYesYes 2Yes 2Yes 2Yes 2
Resource GovernorYesNoNoNoNo
Partitioned Table ParallelismYesNoNoNoNo
Multiple Filestream containersYesYes 2Yes 2Yes 2Yes 2
NUMA Aware and Large Page Memory and Buffer Array AllocationYesNoNoNoNo
Buffer Pool ExtensionYesYesNoNoNo
IO Resource GovernanceYesNoNoNoNo
Delayed DurabilityYesYesYesYesYes

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 degrees of parallelism is limited. The degrees 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 SP1.

3 This feature is not included in the LocalDB installation option.

FeatureEnterpriseStandardWebExpressExpress with Advanced Services
Row-level securityYesYesYes 1Yes 1Yes 1
Always EncryptedYesYes 1Yes 1Yes 1Yes 1
Dynamic data maskingYesYesYes 1Yes 1Yes 1
Basic auditingYesYesYesYesYes
Fine grained auditingYesYes 1Yes 1Yes 1Yes 1
Transparent database encryptionYesNoNoNoNo
Extensible key managementYesNoNoNoNo
User-defined rolesYesYesYesYesYes
Contained databasesYesYesYesYesYes
Encryption for backupsYesYesNoNoNo

1 Applies to SQL Server 2016 SP1.

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Heterogeneous subscribersYesYesNoNoNo
Merge replicationYesYesYes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)
Oracle publishingYesNoNoNoNo
Peer to peer transactional replicationYesNoNoNoNo
Snapshot replicationYesYesYes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)
SQL Server change trackingYesYesYesYesYes
Transactional replicationYesYesYes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)
Transactional replication to AzureYesYesYesNoNo
Transactional replication updateable subscriptionYesNoNoNoNo
FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
SQL Management Objects (SMO)YesYesYesYesYes
SQL Configuration ManagerYesYesYesYesYes
SQL CMD (Command Prompt tool)YesYesYesYesYes
Distributed Replay - Admin ToolYesYesYesYesNo
Distribute Replay - ClientYesYesYesNoNo
Distributed Replay - ControllerYes (Up to 16 clients)Yes (1 client)Yes (1 client)NoNo
SQL ProfilerYesYesNo 1No 1No 1
SQL Server AgentYesYesYesNoNo
Microsoft System Center Operations Manager Management PackYesYesYesNoNo
Database Tuning Advisor (DTA)YesYes 2Yes 2NoNo

1 SQL Server Web, SQL Server Express, SQL Server Express with Tools, and SQL Server Express with Advanced Services can be profiled using SQL Server Standard and SQL Server Enterprise editions.

2 Tuning enabled only on Standard edition features

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
User instancesNoNoNoYesYes
LocalDBNoNoNoYesNo
Dedicated admin connectionYesYesYesYes with trace flagYes with trace flag
PowerShell scripting supportYesYesYesYesYes
SysPrep support 1YesYesYesYesYes
Support for data-tier application component operations - extract, deploy, upgrade, deleteYesYesYesYesYes
Policy automation (check on schedule and change)YesYesYesNoNo
Performance data collectorYesYesYesNoNo
Able to enroll as a managed instance in multi-instance managementYesYesYesNoNo
Standard performance reportsYesYesYesNoNo
Plan guides and plan freezing for plan guidesYesYesYesNoNo
Direct query of indexed views (using NOEXPAND hint)YesYesYesYesYes
Automatic indexed views maintenanceYesYesYesNoNo
Distributed partitioned viewsYesNoNoNoNo
Parallel indexed operationsYesNoNoNoNo
Automatic use of indexed view by query optimizerYesNoNoNoNo
Parallel consistency checkYesNoNoNoNo
SQL Server Utility Control PointYesNoNoNoNo
Buffer pool extensionYesYesNoNoNo

1 For more information, see Considerations for Installing SQL Server Using SysPrep.

2 Applies to SQL Server 2016 SP1.

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Microsoft Visual Studio integrationYesYesYesYesYes
Intellisense (Transact-SQL and MDX)YesYesYesYesYes
SQL Server Data Tools (SSDT)YesYesYesYesNo
MDX edit, debug, and design toolsYesYesNoNoNo
FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Basic R integrationYesYesYesYesNo
Advanced R integrationYesNoNoNoNo
R Server (Standalone)YesNoNoNoNo
Polybase compute nodeYesYes 1Yes 1, 2Yes 1, 2Yes 1, 2
Polybase head nodeYesNoNoNoNo
JSONYesYesYesYesYes
Query StoreYesYesYesYesYes
TemporalYesYesYesYesYes
Common Language Runtime (CLR) IntegrationYesYesYesYesYes
Native XML supportYesYesYesYesYes
XML indexingYesYesYesYesYes
MERGE & UPSERT capabilitiesYesYesYesYesYes
FILESTREAM supportYesYesYesYesYes
FileTableYesYesYesYesYes
Date and Time datatypesYesYesYesYesYes
Internationalization supportYesYesYesYesYes
Full-text and semantic searchYesYesYesYesNo
Specification of language in queryYesYesYesYesNo
Service Broker (messaging)YesYesNo (Client only)No (Client only)No (Client only)
Transact-SQL endpointsYesYesYesNoNo

1 Scale out with multiple compute nodes requires a head node.

2 Applies to SQL Server 2016 SP1.

For info about the Integration Services (SSIS) features supported by the editions of SQL Server 2016, see Integration Services Features Supported by the Editions of SQL Server 2016.

For information about the Master Data Services and Data Quality Services features supported by the editions of SQL Server 2016, see Master Data Services and Data Quality Services Features Supported by the Editions of SQL Server 2016.

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress
Create cubes without a databaseYesYesNoNoNo
Auto-generate staging and data warehouse schemaYesYesNoNoNo
Change data captureYesYes 1Yes 1NoNo
Star join query optimizationsYesNoNoNoNo
Scalable read-only Analysis Services configurationYesNoNoNoNo
Parallel query processing on partitioned tables and indexesYesNoNoNoNo
xVelocity memory optimized columnstore indexesYesNoNoNoNo
Global batch aggregationYesNoNoNoNo

1 Applies to SQL Server 2016 SP1.

For information about the Analysis Services features supported by the editions of SQL Server 2016, see Analysis Services Features Supported by the Editions of SQL Server 2016.

For information about the Analysis Services features supported by the editions of SQL Server 2016, see Analysis Services Features Supported by the Editions of SQL Server 2016.

For information about the Analysis Services features supported by the editions of SQL Server 2016, see Analysis Services Features Supported by the Editions of SQL Server 2016.

For information about the Power Pivot for SharePoint features supported by the editions of SQL Server 2016, see Analysis Services Features Supported by the Editions of SQL Server 2016.

For information about the Data Mining features supported by the editions of SQL Server 2016, see Analysis Services Features Supported by the Editions of SQL Server 2016.

For information about the Reporting Services features supported by the editions of SQL Server 2016, see Reporting Services Features Supported by the Editions of SQL Server 2016.

For information about the Business Intelligence Client features supported by the editions of SQL Server 2016, see Analysis Services Features Supported by the Editions of SQL Server 2016 or Reporting Services Features Supported by the Editions of SQL Server 2016.

Feature NameEnterpriseStandardWebExpress with Advanced ServicesExpress
Spatial indexesYesYesYesYesYes
Planar and geodetic datatypesYesYesYesYesYes
Advanced spatial librariesYesYesYesYesYes
Import/export of industry-standard spatial data formatsYesYesYesYesYes
Feature NameEnterpriseStandardWebExpress with Advanced ServicesExpress
SQL Server Migration AssistantYesYesYesYesYes
Database mailYesYesYesNoNo
Feature NameEnterpriseStandardWebExpress with Advanced ServicesExpress
StreamInsightStreamInsight Premium EditionStreamInsight Standard EditionStreamInsight Standard EditionNoNo
StreamInsight HAStreamInsight Premium EditionNoNoNoNo

Download SSMS  Download the latest version of SQL Server Management Studio

Product Specifications for SQL Server 2016
Installation for SQL Server 2016