The database comes back online, and a memory-optimized table is empty. SCHEMA_ONLY deliberately preserves the definition without preserving its rows. Choose that behavior only for data the application can lose and rebuild.
Define What Must Survive
I ask what happens if every row disappears before choosing memory-optimized durability. A cache and an accepted payment record have different answers. Their access speed doesn't settle their survival requirement.
SCHEMA_AND_DATA preserves both the table definition and committed durable data. SCHEMA_ONLY preserves the definition but not the row contents across database recovery. Both use memory-optimized structures for active access.
A schema-only table isn't a local temporary table. Its definition is shared and persists in the user database. Applications must design row ownership and cleanup explicitly.
It also isn't an automatic per-session container. Two connections can read the same permitted rows. Add keys or access rules when the application expects isolated session state.
Keep this choice in the data contract. A table name containing Cache doesn't make loss acceptable. The application must have a documented way to rebuild or abandon its contents.
Prepare an Isolated Durability Test
The examples need In-Memory OLTP support and a separate disposable database. Use an edition and configuration supporting the feature. The service needs filesystem permissions for the memory-optimized container.
Create the parent directory first and replace the placeholder path. The new container path should not collide with an existing object. Never run the later offline test against a shared application database.
The setup creates two tables differing in durability. Their sample inputs make the later behavior easy to inspect. Each table holds one sample row before the offline test.
The primary keys are nonclustered indexes suitable for these memory-optimized tables. This test doesn't need a hash allocation experiment. Keep its purpose limited to survival across recovery.
Run the setup once from a connection allowed to create and configure the test database. If the name already exists, stop and choose another isolated name. Don't replace existing data to reuse the example.
CREATE DATABASE [MemoryDurabilityDemo];
GO
ALTER DATABASE [MemoryDurabilityDemo]
ADD FILEGROUP [MemoryDurabilityFiles] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [MemoryDurabilityDemo]
ADD FILE(NAME = N'MemoryDurabilityContainer', FILENAME = N'C:\SqlData\MemoryDurabilityContainer-new')
TO FILEGROUP [MemoryDurabilityFiles];
GO
USE [MemoryDurabilityDemo];
GO
CREATE TABLE dbo.TransientItemsDemo
(ItemId int NOT NULL PRIMARY KEY NONCLUSTERED, ItemValue nvarchar(80) NOT NULL)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
CREATE TABLE dbo.DurableItemsDemo
(ItemId int NOT NULL PRIMARY KEY NONCLUSTERED, ItemValue nvarchar(80) NOT NULL)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
INSERT dbo.TransientItemsDemo VALUES(1, N'Transient sample');
INSERT dbo.DurableItemsDemo VALUES(1, N'Durable sample');
SELECT name, is_memory_optimized, durability_desc
FROM sys.tables WHERE name IN (N'TransientItemsDemo', N'DurableItemsDemo');Understand the Durable Storage Path
Durable memory-optimized changes use the transaction log. Checkpoint data and delta files persist row versions and deletion information. Recovery combines those files with the necessary log records.
Active queries still use the in-memory structures. The checkpoint files aren't ordinary pages read by each lookup. Their purpose is durability and recovery.
Indexes are reconstructed for memory-optimized recovery rather than stored as ordinary persisted index pages. Plan memory capacity for the recovered table and its indexes. Storage capacity alone doesn't supply that memory.
Normal log backup and checkpoint activity remain important. Retained row versions and checkpoint files have a lifecycle. A memory-optimized durable database still needs a recovery plan.
Fully durable commits provide the durability guarantee. Delayed durability changes when commit records become persistent. Review that separate setting before making an unconditional statement about every acknowledged commit.
Understand What SCHEMA_ONLY Avoids
SCHEMA_ONLY row changes don't require durable row logging and checkpoint storage. The schema itself remains durable metadata. The choice deliberately removes recovery of the row contents.
That can reduce log-related work for transient staging or caches. It doesn't remove memory capacity requirements. It also doesn't eliminate transaction conflict handling within the active database.
A transaction against the table still follows its supported transactional rules. Schema-only doesn't mean arbitrary partially coordinated application behavior is acceptable. Test the write paths and retry handling.
I use this option when missing rows have a clean recovery meaning. A cache miss can lead to rebuilding a value. A missing business submission needs another durable source.
Which system owns the authoritative value? If this table is the only owner, schema-only loss is unacceptable. Labeling it temporary doesn't supply another copy.
Take Only the Test Database Offline
The next commands deliberately take the disposable database offline and online. Run them from master after closing its other test connections. Ensure neither application jobs nor users depend on that database.
The commands omit forced rollback of other sessions. If the operation waits, investigate the remaining test connections. Don't escalate to terminating unrelated sessions to complete a demonstration.
Taking the database offline unloads its active memory-optimized rows. Bringing it online invokes the relevant recovery behavior. The schema-only table returns empty while its definition remains.
The durable table recovers committed rows through its persistence path. Read both tables after the operation. The schema-only count returns 0, and the durable count returns 1.
The distinction also applies to a service restart or relevant failover recovery. A replica doesn't make schema-only rows durable. Test the specific high-availability topology before promising seamless session survival.
USE master;
GO
ALTER DATABASE [MemoryDurabilityDemo] SET OFFLINE;
ALTER DATABASE [MemoryDurabilityDemo] SET ONLINE;
GO
USE [MemoryDurabilityDemo];
GO
SELECT COUNT_BIG(*) AS TransientRows FROM dbo.TransientItemsDemo;
SELECT COUNT_BIG(*) AS DurableRows FROM dbo.DurableItemsDemo;
SELECT name, durability_desc FROM sys.tables
WHERE name IN (N'TransientItemsDemo', N'DurableItemsDemo');Choose a Suitable Use for SCHEMA_ONLY
Staging data can use schema-only storage when the original input remains available. Restart the load from that source after recovery. Track accepted completion in a durable location when required.
Session state is another candidate when losing sessions is an accepted application behavior. Users then need a deliberate reconnect or sign-in path. Don't let the database's empty table surprise the application.
Caches require expiry and rebuilding logic. A restart is one cause of missing values, not the only cause. The read path should handle a miss normally.
I check those paths before recommending the durability change. A successful hot lookup says nothing about the next cold start. The missing-data path is part of the feature.
Keep permanent definitions created during deployment rather than repeatedly creating and dropping tables at runtime. Their creation has its own compilation and recovery effects. Schema-only isn't permission to ignore lifecycle costs.
Test SCHEMA_ONLY Recovery as Part of the Contract
Measure restart and repopulation behavior on your test system. Include concurrent clients arriving after recovery. They shouldn't all assume the former cache still exists.
SCHEMA_ONLY is useful when transient loss is explicit and recoverable. SCHEMA_AND_DATA is appropriate when committed contents must survive. Choose from that requirement before comparing performance.
Record the selected durability beside the schema and application recovery rule. Then test it through the offline-online rehearsal. An empty cache is a feature only when someone planned for it.
Related reading on this blog: Memory-Optimized Table Variables and How to Find the In-Memory OLTP Tables Memory Usage on the Server.
Schema-only storage is not durable business memory, it is a persistent definition for disposable rows.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
