MS-SQL Server 2016 Features

SQL 2016 is packed with features such as Always Encrypted, Dynamic Data Masking, temporal tables, stretched tables, Query Store, Live Query stats and more. From our personal point of view, SQL Server 2016 is indeed an interesting release to mention. Thanks to all the testing we did with the various features of SQL Server 2016.

GeoPITS brings you the comprehensive list of SQL Server 2016 features that are supported in different editions (Enterprise Standard, Web, Express Adv, Express.)

FeaturesEnterpriseStandardWebExpress (Adv.)Express
Maximum compute capacity used by a single instance - SQL Server Database Engine Operating system maximum Limited to lesser of 4 sockets or 24 cores Limited to lesser of 4 sockets or 16 cores Limited to lesser of 1 socket or 4 cores Limited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance - Analysis Services or Reporting Services Operating system maximum Limited to lesser of 4 sockets or 24 cores Limited to lesser of 4 sockets or 16 cores Limited to lesser of 1 socket or 4 cores Limited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool per instance of SQL Server Database Engine Operating System Maximum 128 GB 64 GB 1410 MB 1410 MB
Maximum capacity for buffer pool extension per instance of SQL Server Database Engine 32 * (max server memory configuration) 4 * (max server memory configuration)
Maximum memory for Columnstore segment cache per instance of SQL Server Database Engine Unlimited memory 32 GB 16 GB 352 MB 352 MB
Maximum memory-optimized data size per database in SQL Server Database Engine Unlimited memory 32 GB 16 GB 352 MB 352 MB
Maximum memory utilized per instance of Analysis Services Operating System Maximum Tabular: 16 GB MOLAP: 64 GB
Maximum memory utilized per instance of Reporting Services Operating System Maximum 64 GB 64 GB 4 GB
Maximum relational database size 524 PB 524 PB 524 PB 10 GB 10 GB

FeaturesEnterpriseStandardWebExpress (Adv.)Express
Row-level security Yes Yes Yes 1 Yes 1 Yes 1
Always Encrypted Yes Yes 1 Yes 1 Yes 1 Yes 1
Always Encrypted with Secure Enclaves
Dynamic data masking Yes Yes Yes 1 Yes 1 Yes 1
Server audit Yes Yes Yes Yes Yes
Database audit Yes Yes Yes Yes 1 Yes 1
Transparent database encryption Yes
Extensible key management Yes
User-defined roles Yes Yes Yes Yes Yes
Contained databases Yes Yes Yes Yes Yes
Encryption for backups Yes Yes
Data classification and auditing

FeaturesEnterpriseStandardWebExpress (Adv.)Express
Heterogeneous subscribers Yes Yes
Merge replication Yes Yes Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only)
Oracle publishing Yes
Peer to peer transactional replication Yes
Snapshot replication Yes Yes Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only)
SQL Server change tracking Yes Yes Yes Yes Yes
Transactional replication Yes Yes Yes (Subscriber only) Yes (Subscriber only) Yes (Subscriber only)
Transactional replication to Azure Yes Yes
Transactional replication updateable subscription Yes Yes

FeaturesEnterpriseStandardWebExpress (Adv.)Express
SQL Management Objects (SMO) Yes Yes Yes Yes Yes
SQL Assessment API
SQL Vulnerability Assessment
SQL Configuration Manager Yes Yes Yes Yes Yes
SQL CMD (Command Prompt tool) Yes Yes Yes Yes Yes
Distributed Replay - Admin Tool Yes Yes Yes Yes
Distribute Replay - Client Yes Yes Yes
Distributed Replay - Controller Yes (Up to 16 clients) Yes (1 client) Yes (1 client)
SQL Profiler Yes Yes No No 1 No 1
SQL Server Agent Yes Yes Yes
Microsoft System Center Operations Manager Management Pack Yes Yes Yes
Database Tuning Advisor (DTA) Yes Yes Yes

FeaturesEnterpriseStandardWebExpress (Adv.)Express
Microsoft Visual Studio integration Yes Yes Yes Yes Yes
Intellisense (Transact-SQL and MDX) Yes Yes Yes Yes Yes
SQL Server Data Tools (SSDT) Yes Yes Yes Yes
MDX edit, debug, and design tools Yes Yes

FeaturesEnterpriseStandardWebExpress (Adv.)Express
Basic R integration Yes Yes Yes Yes
Advanced R integration Yes
R Server (Standalone) Yes
Advanced Python integration
Machine Learning Server (Standalone)
PolyBase compute node Yes Yes 1 Yes 1, 2 Yes 1, 2 Yes 1, 2
PolyBase head node Yes
JSON Yes Yes Yes Yes Yes
Query Store Yes Yes Yes Yes Yes
Temporal Yes Yes Yes Yes Yes
Common Language Runtime (CLR) Integration Yes Yes Yes Yes Yes
Java Language Runtime Integration
Native XML support Yes Yes Yes Yes Yes
XML indexing Yes Yes Yes Yes Yes
MERGE & UPSERT capabilities Yes Yes Yes Yes Yes
FILESTREAM support Yes Yes Yes Yes Yes
FileTable Yes Yes Yes Yes Yes
Date and Time datatypes Yes Yes Yes Yes Yes
Internationalization support Yes Yes Yes Yes Yes
Specification of language in query Yes Yes Yes Yes
Service Broker (messaging) Yes Yes No (Client only) No (Client only) No (Client only)
Transact-SQL endpoints Yes Yes Yes
Graph
UTF-8 support

FeaturesEnterpriseStandardWebExpress (Adv.)Express
Spatial indexes Yes Yes Yes Yes Yes
Planar and geodetic datatypes Yes Yes Yes Yes Yes
Advanced spatial libraries Yes Yes Yes Yes Yes
Import/export of industry-standard spatial data formats Yes Yes Yes Yes Yes

FeaturesEnterpriseStandardWebExpress (Adv.)Express
SQL Server Migration Assistant Yes Yes Yes Yes Yes
Database mail Yes Yes Yes

FeaturesEnterpriseStandardWebExpress (Adv.)Express
StreamInsight StreamInsight Premium Edition StreamInsight Standard Edition StreamInsight Standard Edition
StreamInsight HA StreamInsight Premium Edition

We run all kinds of Database services that vow your success