Memory-Optimized Tables -- Non-durable v.s. variable
In-Memory OLTP provides the following objects that can be used for memory-optimizing temp tables and table variables:
Memory-optimized tables
Durability = SCHEMA_ONLY
Memory-optimized table variables
Must be declared in two steps (rather than inline):
CREATE TYPE my_type AS TABLE ...; , then
DECLARE @mytablevariable my_type;.
When we talk about Non-durable, we actually talk about SCHEMA_ONLY. This option ensures durability of the table schema. When SQL Server is restarted or a reconfiguration occurs in an Azure SQL Database, the table schema persists, but data in the table is lost. (This is unlike a table in tempdb, where both the table and its data are lost upon restart.) A typical scenario for creating a non-durable table is to store transient data, such as a staging table for an ETL process. A SCHEMA_ONLY durability avoids both transaction logging and checkpoint, which can significantly reduce I/O operations.
Whereas when using the default SCHEMA_AND_DATA tables, SQL Server provides the same durability guarantees as for disk-based tables: Transactional Durability, Restart Durability, and Media Failure Durability.
A memory-optimized table variable provides great efficiency by using the same memory-optimized algorithm and data structures that are used by memory-optimized tables. The efficiency is maximized when the table variable is accessed from within a natively compiled module. A memory-optimized table variable:
Is stored only in memory, and has no component on disk.
Involves no IO activity.
Involves no tempdb utilization or contention.
Can be passed into a stored proc as a table-valued parameter (TVP).
Must have at least one index, either hash or nonclustered.
For a hash index, the bucket count should ideally be 1-2 times the number of expected unique index keys, but overestimating bucket count is usually fine (up to 10X). For details see Indexes for Memory-Optimized Tables.
If you use temporary tables, table variables, or table-valued parameters, consider conversions of them to leverage memory-optimized tables and table variables to improve performance. The code changes are usually minimal.
You must create two staging database tables. The tables have the following requirements:
You need to select the correct storage mechanism for each table.
Answer:
