Issue link: http://hub-nl.insight.com/i/692679
2 C H A P T E R 1 | Faster queries Reviewing new features for memory-optimized tables In SQL Server 2016, you can implement the following features in memory-optimized tables: FOREIGN KEY constraints between memory-optimized tables, as long as the foreign key references a primary key. CHECK constraints. UNIQUE constraints. Triggers (AFTER) for INSERT/UPDATE/DELETE operations, as long as you use WITH NATIVE_COMPILATION. Columns with large object (LOB) types—varchar(max), nvarchar(max), and varbinary(max). Collation using any code page supported by SQL Server. Indexes for memory-optimized tables now support the following features: UNIQUE indexes. Index keys with character columns using any SQL Server collation. NULLable index key columns. Scaling memory-optimized tables Memory-optimized tables in SQL Server 2016 scale much better than they did when they were introduced. Not only can you put more data into memory-optimized tables, but you can also experience better throughput to support bigger workloads. In the current release, you can store up to 2 terabytes (TB) of data in a memory-optimized table when you create a durable table by using the SCHEMA_AND_DATA option in the CREATE TABLE statement. Furthermore, you can create a memory- optimized table with a row size greater than 8,060 bytes, even without a LOB column, as shown in Example 1-1. Example 1-1: Creating a memory-optimized table with a row size greater than 8,060 bytes CREATE TABLE ProductReviewHistory ( ProductID INT IDENTITY PRIMARY KEY NONCLUSTERED, ProductEnglishDescription NVARCHAR(4000), ProductSpanishDescription NVARCHAR(4000), ProductReviewDate DATETIME, ProductEnglishReviewComments NVARCHAR(4000), ProductSpanishReviewComments NVARCHAR(4000), ) WITH (MEMORY_OPTIMIZED = ON); Note Although SQL Server 2016 supports this new row size for memory-optimized tables, you should create as narrow a table as possible when your requirement is high-performance queries. To further deliver improved performance for memory-optimized tables, SQL Server 2016 supports parallel scans of all indexes, including nonclustered and hash indexes. This capability is especially