Memory-optimized tables are created using CREATE Table (Transact-SQL). Memory-optimized tables are totally durable by default, and, like transactions on (conventional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, remoted, and durable (ACID). Memory-optimized tables and natively compiled stored procedures help solely a subset of Transact-SQL options. Beginning with SQL Server 2016, and in Azure SQL Database, there aren't any limitations for collations or code pages which are particular 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 knowledge is maintained on disk, however just for durability functions. See Creating and Managing Storage for Memory-Optimized Objects for extra details about durable tables. Information in memory-optimized tables is simply read from disk throughout database recovery (for example, after a server restart). For even better performance gains, 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 consumer.
In exchange for the increased efficiency, dedicated transactions that aren't persisted to disk are lost 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 information isn't persisted on disk. Because of this transactions on these tables don't require any disk IO, but the information is misplaced if there is a server crash or failover. In-Memory OLTP is built-in with SQL Server to supply a seamless experience in all areas corresponding to improvement, deployment, manageability, and supportability. A database can comprise in-memory in addition to disk-primarily based objects. Rows in memory-optimized tables are versioned. This implies that each row within the table doubtlessly has multiple versions. All row variations are maintained in the same table knowledge construction. Row versioning is used to allow concurrent reads and writes on the same row. For extra 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 each row has different variations. The table has three rows: r1, r2, and Memory Wave Method r3. 1 has three variations, r2 has two variations, and r3 has 4 versions. Totally different variations of the identical row do not necessarily occupy consecutive memory locations. The completely different row versions will be dispersed all through the table data construction. The memory-optimized desk information structure can be seen as a collection of row variations. Rows in disk-primarily based tables are organized in pages and extents, and individual rows addressed using web page number and page offset, row variations in memory-optimized tables are addressed utilizing 8-byte memory pointers. Through natively compiled stored procedures. Via interpreted Transact-SQL, exterior of a natively compiled stored process. These Transact-SQL statements could also be both inside interpreted saved procedures or 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 will also be accessed with (conventional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables and not using a natively compiled saved process. Some examples of interpreted Transact-SQL entry embrace accessing a memory-optimized desk from a DML set off, advert hoc Transact-SQL batch, view, and desk-valued perform. The following desk summarizes native and interpreted Transact-SQL access for numerous objects. 1You cannot entry a memory-optimized desk or natively compiled stored procedure from the context connection (the connection from SQL Server when executing a CLR module). You may, nevertheless, create and open another connection from which you'll entry memory-optimized tables and natively compiled saved procedures. Delicate data in memory-optimized tables can be protected through the use of All the time 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. Because of this in-place encryption cannot be used, and the initial encryption is completed on the consumer.
At all times Encrypted isn't supported for any column in a Memory Wave Method-optimized table when the table is referenced in a natively compiled module. Communication: An software using many short stored procedure calls would possibly see a smaller efficiency acquire in comparison with an software with fewer calls and more functionality carried out in every saved process. Transact-SQL Execution: In-Memory OLTP achieves the perfect performance when utilizing natively compiled saved procedures somewhat than interpreted saved procedures or query execution. There could be a benefit to accessing memory-optimized tables from such stored procedures. Range Scan vs Point Lookup: Memory-optimized nonclustered indexes assist vary scans and ordered scans. For level lookups, memory-optimized hash indexes have better efficiency than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have higher performance than disk-based mostly indexes. Beginning in SQL Server 2016, the query plan for a memory-optimized desk 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.