Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 12 of 212

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

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016