Memory-optimized tables are created utilizing CREATE Table (Transact-SQL). Memory-optimized tables are fully durable by default, and, like transactions on (conventional) disk-primarily based tables, transactions on memory-optimized tables are fully atomic, consistent, remoted, Memory Wave and durable (ACID). Memory-optimized tables and natively compiled saved 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 are particular to In-Memory OLTP. The primary storage for memory-optimized tables is the main memory. Rows in the table are learn from and written to memory. A second copy of the desk information is maintained on disk, however just for sturdiness functions. See Creating and Managing Storage for Memory-Optimized Objects for Memory Wave more information about durable tables. Data in memory-optimized tables is only read from disk during database restoration (for example, after a server restart). For even higher efficiency positive factors, In-Memory OLTP helps durable tables with transaction sturdiness delayed. Delayed durable transactions are saved to disk quickly after the transaction commits and control is returned to the consumer.

In alternate for the increased performance, dedicated 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 also supports non-durable memory-optimized tables, which aren't logged and their information isn't persisted on disk. This means that transactions on these tables don't require any disk IO, but the info is lost if there is a server crash or failover. In-Memory OLTP is integrated with SQL Server to offer a seamless expertise in all areas comparable to development, deployment, manageability, and supportability. A database can include in-memory in addition to disk-based mostly objects. Rows in memory-optimized tables are versioned. This means that each row in the desk potentially has a number of variations. All row variations are maintained in the identical table information structure. Row versioning is used to permit concurrent reads and writes on the identical row. For extra details about concurrent reads and writes on the same row, see Transactions with Memory-Optimized Tables.

The following determine illustrates multi-versioning. The figure exhibits a desk with three rows and each row has totally different variations. The desk has three rows: r1, r2, and cognitive enhancement tool r3. 1 has three variations, r2 has two versions, and r3 has 4 versions. Completely different variations of the identical row do not necessarily occupy consecutive memory locations. The totally different row versions might be dispersed all through the table information structure. The memory-optimized desk data construction could be seen as a collection of row variations. Rows in disk-primarily based tables are organized in pages and extents, and particular person rows addressed using web 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 means of interpreted Transact-SQL, outdoors of a natively compiled saved procedure. These Transact-SQL statements could also be either inside interpreted stored procedures or they could also be ad hoc Transact-SQL statements. Memory-optimized tables might be accessed most effectively from natively compiled stored procedures (Natively Compiled Stored Procedures).

Memory-optimized tables can be accessed with (conventional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables without a natively compiled stored process. Some examples of interpreted Transact-SQL entry include accessing a memory-optimized table from a DML set off, advert hoc Transact-SQL batch, view, and desk-valued function. The following desk summarizes native and interpreted Transact-SQL access for varied objects. 1You cannot 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, nonetheless, create and open one other connection from which you'll entry memory-optimized tables and natively compiled stored procedures. Delicate data in memory-optimized tables will be protected through the use of At all times Encrypted. When using At all times Encrypted with secure enclaves, the usage of enclave-enabled keys for columns in memory-optimized tables isn't supported. This means that in-place encryption can't be used, and the initial encryption is completed on the shopper.

At all times Encrypted isn't supported for any column in a memory-optimized table when the desk is referenced in a natively compiled module. Communication: An utility using many short saved process calls would possibly see a smaller efficiency gain in comparison with an application with fewer calls and more performance applied in every stored procedure. Transact-SQL Execution: In-Memory OLTP achieves the very best efficiency when utilizing natively compiled saved procedures rather than interpreted saved procedures or query execution. There could be a profit to accessing memory-optimized tables from such saved procedures. Vary Scan vs Point Lookup: Memory-optimized nonclustered indexes assist range scans and ordered scans. For level lookups, memory-optimized hash indexes have better performance than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have better efficiency than disk-based mostly 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, and so they exist solely in memory.