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.)
| Features | Enterprise | Standard | Web | Express (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 |
| Features | Enterprise | Standard | Web | Express (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 |
| Features | Enterprise | Standard | Web | Express (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 |
| Features | Enterprise | Standard | Web | Express (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 |
| Features | Enterprise | Standard | Web | Express (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 |
| Features | Enterprise | Standard | Web | Express (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 |
| Full-text and semantic search | 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 |
| Features | Enterprise | Standard | Web | Express (Adv.) | Express |
|---|---|---|---|---|---|
| Create cubes without a database | Yes | Yes | |||
| Autogenerate staging and data warehouse schema | Yes | Yes | |||
| Change data capture | Yes | Yes 1 | |||
| Star join query optimizations | Yes | ||||
| Scalable read-only Analysis Services configuration | Yes | ||||
| Parallel query processing on partitioned tables and indexes | Yes | ||||
| Global batch aggregation | Yes |
| Features | Enterprise | Standard | Web | Express (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 |
| Features | Enterprise | Standard | Web | Express (Adv.) | Express |
|---|---|---|---|---|---|
| SQL Server Migration Assistant | Yes | Yes | Yes | Yes | Yes |
| Database mail | Yes | Yes | Yes |
| Features | Enterprise | Standard | Web | Express (Adv.) | Express |
|---|---|---|---|---|---|
| StreamInsight | StreamInsight Premium Edition | StreamInsight Standard Edition | StreamInsight Standard Edition | ||
| StreamInsight HA | StreamInsight Premium Edition |
