Memory-optimized tables are created utilizing CREATE Desk (Transact-SQL). Memory-optimized tables are fully durable by default, and, like transactions on (traditional) disk-primarily based tables, transactions on memory-optimized tables are totally atomic, constant, isolated, and durable (ACID). Memory-optimized tables and natively compiled saved procedures help only a subset of Transact-SQL options. Starting with SQL Server 2016, and in Azure SQL Database, there are not any limitations for collations or code pages which are specific to In-Memory OLTP. The primary storage for boost brain function memory-optimized tables is the principle memory. Rows within the table are learn from and written to memory. A second copy of the desk knowledge is maintained on disk, but only for sturdiness purposes. See Creating and Managing Storage for Memory-Optimized Objects for extra information about durable tables. Data in memory-optimized tables is just read from disk during database restoration (for instance, after a server restart). For even higher efficiency positive factors, In-Memory OLTP helps durable tables with transaction durability delayed. Delayed durable transactions are saved to disk soon after the transaction commits and management is returned to the client.

In trade for the elevated efficiency, committed transactions that are not 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 data is not persisted on disk. Because of this transactions on these tables do not require any disk IO, however the data is lost if there's a server crash or failover. In-Memory OLTP is integrated with SQL Server to provide a seamless expertise in all areas corresponding to growth, Memory Wave deployment, manageability, and Memory Wave supportability. A database can include in-memory as well as disk-primarily based objects. Rows in memory-optimized tables are versioned. This means that each row in the table probably has a number of variations. All row variations are maintained in the same desk data construction. Row versioning is used to allow concurrent reads and writes on the same row. For extra information about concurrent reads and writes on the identical row, see Transactions with Memory-Optimized Tables.

The following determine illustrates multi-versioning. The determine exhibits a table with three rows and each row has different versions. The table has three rows: r1, r2, and r3. 1 has three versions, r2 has two versions, and r3 has four variations. Different variations of the identical row do not essentially occupy consecutive memory places. The completely different row variations may be dispersed all through the table knowledge construction. The memory-optimized table information structure could be seen as a set of row variations. Rows in disk-based tables are organized in pages and extents, and particular person rows addressed using page quantity and web page offset, row variations in memory-optimized tables are addressed utilizing 8-byte memory pointers. Via natively compiled stored procedures. By means of interpreted Transact-SQL, outside of a natively compiled saved process. These Transact-SQL statements could also be both inside interpreted saved procedures or they may be ad hoc Transact-SQL statements. Memory-optimized tables could be accessed most efficiently from natively compiled saved procedures (Natively Compiled Stored Procedures).

Memory-optimized tables can also be accessed with (traditional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables with out a natively compiled saved process. Some examples of interpreted Transact-SQL access include accessing a memory-optimized desk from a DML trigger, ad hoc Transact-SQL batch, view, boost brain function and desk-valued perform. The next table summarizes native and interpreted Transact-SQL entry for varied objects. 1You cannot entry a memory-optimized desk or natively compiled saved process from the context connection (the connection from SQL Server when executing a CLR module). You may, nonetheless, create and open one other connection from which you'll access memory-optimized tables and natively compiled saved procedures. Sensitive information in memory-optimized tables can be protected by using At all times Encrypted. When using All the time Encrypted with safe enclaves, using enclave-enabled keys for columns in memory-optimized tables isn't supported. Because of this in-place encryption cannot be used, and the initial encryption is completed on the shopper.

All the time Encrypted isn't supported for any column in a memory-optimized desk when the table is referenced in a natively compiled module. Communication: An application using many short stored procedure calls may see a smaller performance gain in comparison with an utility with fewer calls and extra performance carried out in every stored process. Transact-SQL Execution: In-Memory OLTP achieves the perfect efficiency when using natively compiled saved procedures relatively than interpreted saved procedures or question execution. There could be a benefit to accessing memory-optimized tables from such stored procedures. Range Scan vs Level Lookup: Memory-optimized nonclustered indexes help vary scans and ordered scans. For point lookups, memory-optimized hash indexes have better performance than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have better performance than disk-based mostly 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, they usually exist only in memory.