Memory-optimized tables are created utilizing CREATE Desk (Transact-SQL). Memory-optimized tables are totally durable by default, and, like transactions on (traditional) disk-primarily based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). Memory-optimized tables and Memory Wave natively compiled stored procedures support only a subset of Transact-SQL features. Starting with SQL Server 2016, and in Azure SQL Database, there aren't any limitations for collations or code pages which are specific to In-Memory OLTP. The first storage for memory-optimized tables is the primary memory. Rows within the desk are read from and written to memory. A second copy of the table information is maintained on disk, however only for sturdiness functions. See Creating and Memory Wave Managing Storage for Memory-Optimized Objects for extra details about durable tables. Information in memory-optimized tables is simply read from disk throughout database restoration (for instance, after a server restart). For even larger efficiency beneficial properties, In-Memory OLTP supports durable tables with transaction sturdiness delayed. Delayed durable transactions are saved to disk soon after the transaction commits and control is returned to the shopper.

In change for the increased efficiency, committed transactions that aren't persisted to disk are misplaced in a server crash or MemoryWave fail over. Besides the default durable memory-optimized tables, SQL Server also helps non-durable memory-optimized tables, which aren't logged and their information is not persisted on disk. Which means that transactions on these tables don't require any disk IO, but the data is lost if there is a server crash or failover. In-Memory OLTP is built-in with SQL Server to provide a seamless expertise in all areas reminiscent of development, deployment, manageability, and supportability. A database can include in-memory in addition to disk-based objects. Rows in memory-optimized tables are versioned. This means that every row in the table doubtlessly has multiple variations. All row versions are maintained in the identical desk information construction. Row versioning is used to allow concurrent reads and writes on the identical row. For extra information about concurrent reads and writes on the identical row, see Transactions with Memory-Optimized Tables.

The next figure illustrates multi-versioning. The determine exhibits a desk with three rows and every row has different versions. The desk has three rows: r1, r2, and r3. 1 has three versions, r2 has two variations, and r3 has four variations. Completely different versions of the identical row do not essentially occupy consecutive memory areas. The completely different row variations can be dispersed all through the table information construction. The memory-optimized table knowledge structure may be seen as a set of row variations. Rows in disk-based mostly tables are organized in pages and extents, and individual rows addressed utilizing web page quantity and web page offset, row variations in memory-optimized tables are addressed using 8-byte memory pointers. By way of natively compiled saved procedures. By interpreted Transact-SQL, outside of a natively compiled saved process. These Transact-SQL statements could also be either inside interpreted stored procedures or MemoryWave they may be advert hoc Transact-SQL statements. Memory-optimized tables might be accessed most efficiently from natively compiled stored procedures (Natively Compiled Saved Procedures).

Memory-optimized tables can be accessed with (conventional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables and not using a natively compiled saved procedure. Some examples of interpreted Transact-SQL entry include accessing a memory-optimized table from a DML set off, ad hoc Transact-SQL batch, view, and desk-valued perform. The next desk summarizes native and interpreted Transact-SQL entry for various objects. 1You can't entry a memory-optimized desk or natively compiled saved procedure from the context connection (the connection from SQL Server when executing a CLR module). You possibly can, nevertheless, create and open one other connection from which you'll be able to access memory-optimized tables and natively compiled saved procedures. Delicate data in memory-optimized tables could be protected by utilizing All the time Encrypted. When using At all times Encrypted with secure enclaves, the use of enclave-enabled keys for columns in memory-optimized tables isn't supported. This means that in-place encryption cannot be used, and the initial encryption is finished on the shopper.

Always Encrypted is not supported for any column in a memory-optimized desk when the table is referenced in a natively compiled module. Communication: An utility utilizing many quick saved procedure calls might see a smaller efficiency achieve compared to an application with fewer calls and more functionality applied in every stored process. Transact-SQL Execution: In-Memory OLTP achieves the perfect performance when utilizing natively compiled saved procedures rather than interpreted stored procedures or question execution. There is usually a benefit to accessing memory-optimized tables from such stored procedures. Vary Scan vs Point Lookup: Memory-optimized nonclustered indexes assist range scans and ordered scans. For level lookups, memory-optimized hash indexes have higher performance than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have better performance than disk-based indexes. Beginning in SQL Server 2016, the query plan for a memory-optimized table can scan the desk in parallel. This improves the efficiency of analytical queries. Index operations: Index operations aren't logged, they usually exist solely in memory.