Memory-optimized tables are created using CREATE Desk (Transact-SQL). Memory-optimized tables are absolutely durable by default, and, like transactions on (conventional) disk-based mostly tables, transactions on memory-optimized tables are absolutely atomic, constant, remoted, and durable (ACID). Memory-optimized tables and natively compiled stored procedures help solely a subset of Transact-SQL options. Starting with SQL Server 2016, and in Azure SQL Database, there aren't any limitations for collations or code pages which can be specific to In-Memory OLTP. The primary storage for memory-optimized tables is the primary memory. Rows within the table are learn from and written to memory. A second copy of the desk information is maintained on disk, however just for sturdiness purposes. See Creating and Managing Storage for Memory Wave-Optimized Objects for extra information about durable tables. Data in memory-optimized tables is just read from disk throughout database restoration (for instance, after a server restart). For even greater efficiency beneficial properties, In-Memory OLTP supports durable tables with transaction durability delayed. Delayed durable transactions are saved to disk quickly after the transaction commits and control is returned to the shopper.
In trade for the elevated performance, committed transactions that aren't persisted to disk are misplaced in a server crash or fail over. In addition to the default durable memory-optimized tables, SQL Server additionally helps non-durable memory-optimized tables, which are not logged and their data isn't persisted on disk. Because of this transactions on these tables don't require any disk IO, however the info is misplaced if there's a server crash or failover. In-Memory OLTP is built-in with SQL Server to supply a seamless experience in all areas similar to development, deployment, manageability, and Memory Wave Routine supportability. A database can comprise in-memory in addition to disk-based mostly objects. Rows in memory-optimized tables are versioned. This means that each row in the desk doubtlessly has multiple variations. All row variations are maintained in the identical table data structure. Row versioning is used to allow concurrent reads and writes on the identical row. For more details about concurrent reads and writes on the same row, see Transactions with Memory-Optimized Tables.
The following determine illustrates multi-versioning. The determine exhibits a table with three rows and Memory Wave every row has completely different variations. The table has three rows: r1, r2, and r3. 1 has three versions, r2 has two versions, and r3 has 4 versions. Different versions of the identical row do not necessarily occupy consecutive memory locations. The totally different row variations could be dispersed throughout the table knowledge structure. The memory-optimized desk data structure could be seen as a group of row variations. Rows in disk-based tables are organized in pages and extents, and particular person rows addressed utilizing page number and page offset, row variations in memory-optimized tables are addressed using 8-byte memory pointers. By means of natively compiled stored procedures. By way of interpreted Transact-SQL, outdoors of a natively compiled saved procedure. These Transact-SQL statements may be both inside interpreted saved procedures or they could also be ad hoc Transact-SQL statements. Memory-optimized tables could be accessed most effectively from natively compiled stored procedures (Natively Compiled Saved Procedures).
Memory-optimized tables will also be accessed with (traditional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables with no natively compiled saved process. Some examples of interpreted Transact-SQL access embody accessing a memory-optimized desk from a DML trigger, advert hoc Transact-SQL batch, view, and table-valued operate. The following desk summarizes native and interpreted Transact-SQL entry for various objects. 1You can't access a memory-optimized desk or natively compiled stored procedure from the context connection (the connection from SQL Server when executing a CLR module). You'll be able to, however, create and open one other connection from which you'll access memory-optimized tables and natively compiled saved procedures. Delicate information in memory-optimized tables may be protected by utilizing At all times Encrypted. When utilizing At all times Encrypted with secure enclaves, the use of enclave-enabled keys for columns in memory-optimized tables is not supported. Which means that in-place encryption can't be used, and the initial encryption is done on the consumer.
Always Encrypted isn't supported for any column in a Memory Wave Routine-optimized desk when the desk is referenced in a natively compiled module. Communication: An software using many quick stored procedure calls would possibly see a smaller efficiency achieve compared to an utility with fewer calls and extra functionality applied in every saved process. Transact-SQL Execution: In-Memory OLTP achieves the most effective performance when using natively compiled saved procedures moderately than interpreted saved procedures or question execution. There can be a profit to accessing memory-optimized tables from such saved procedures. Vary Scan vs Level Lookup: Memory-optimized nonclustered indexes support range scans and ordered scans. For point lookups, memory-optimized hash indexes have higher efficiency than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have higher performance than disk-based indexes. Starting in SQL Server 2016, the query plan for a memory-optimized table can scan the desk in parallel. This improves the performance of analytical queries. Index operations: Index operations aren't logged, and so they exist only in memory.