Memory-optimized tables are created utilizing CREATE Desk (Transact-SQL). Memory-optimized tables are absolutely durable by default, and, like transactions on (traditional) disk-based mostly tables, transactions on memory-optimized tables are fully atomic, constant, remoted, and durable (ACID). Memory-optimized tables and natively compiled saved procedures help only a subset of Transact-SQL features. Starting with SQL Server 2016, and in Azure SQL Database, there are no limitations for collations or code pages which are specific to In-Memory OLTP. The primary storage for memory-optimized tables is the principle memory. Rows in the table are learn from and written to memory. A second copy of the desk data is maintained on disk, however only for durability functions. See Creating and Managing Storage for Memory-Optimized Objects for extra details about durable tables. Data in memory-optimized tables is only learn from disk throughout database restoration (for example, after a server restart). For even greater efficiency gains, In-Memory OLTP helps durable tables with transaction durability delayed. Delayed durable transactions are saved to disk soon after the transaction commits and MemoryWave Official management is returned to the client.

In change for the increased performance, committed transactions that aren't persisted to disk are lost in a server crash or fail over. Besides the default durable memory-optimized tables, SQL Server also helps non-durable memory-optimized tables, which are not logged and their knowledge is not persisted on disk. Because of this transactions on these tables do not require any disk IO, however the data is misplaced if there's a server crash or failover. In-Memory OLTP is built-in with SQL Server to provide a seamless experience in all areas akin to improvement, deployment, manageability, and supportability. A database can include in-memory in addition to disk-based objects. Rows in memory-optimized tables are versioned. This implies that every row within the desk potentially has a number of versions. All row variations are maintained in the same desk data construction. Row versioning is used to permit concurrent reads and writes on the same row. For more details about concurrent reads and writes on the identical row, see Transactions with Memory-Optimized Tables.

The following figure illustrates multi-versioning. The determine exhibits a table with three rows and every row has totally different variations. The desk has three rows: r1, r2, and r3. 1 has three versions, r2 has two variations, and r3 has four variations. Different versions of the identical row do not essentially occupy consecutive memory places. The different row versions can be dispersed throughout the desk knowledge construction. The memory-optimized table data structure will be seen as a set of row versions. Rows in disk-based tables are organized in pages and extents, Memory Wave and particular person rows addressed utilizing page number and web page offset, row versions in memory-optimized tables are addressed utilizing 8-byte memory pointers. By natively compiled saved procedures. By interpreted Transact-SQL, outside of a natively compiled stored procedure. These Transact-SQL statements may be either inside interpreted saved procedures or they could also be advert hoc Transact-SQL statements. Memory-optimized tables will be accessed most efficiently from natively compiled stored procedures (Natively Compiled Saved Procedures).

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

Always Encrypted isn't supported for any column in a memory-optimized desk when the desk is referenced in a natively compiled module. Communication: An application utilizing many quick stored process calls might see a smaller efficiency gain compared to an software with fewer calls and more performance applied in each saved procedure. Transact-SQL Execution: In-Memory OLTP achieves one of the best performance when utilizing natively compiled saved procedures somewhat than interpreted stored procedures or query execution. There is usually a profit to accessing memory-optimized tables from such stored procedures. Range Scan vs Level Lookup: Memory-optimized nonclustered indexes assist vary 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 mostly indexes. Starting in SQL Server 2016, the question plan for a memory-optimized desk can scan the table in parallel. This improves the performance of analytical queries. Index operations: Index operations aren't logged, and so they exist only in memory.