1

I was trying to learn querystore. When going through few queries involved in query store one of the query is below

DELETE TOP (1000000)
FROM [sys].[memory_optimized_history_table_1179151246]
 WITH (SNAPSHOT) 

Full query:

(@rowcount INT OUTPUT) DELETE TOP (1000000) FROM [sys].[memory_optimized_history_table_1179151246] WITH (SNAPSHOT) 
OUTPUT 
DELETED.[ColdRoomTemperatureID], DELETED.[ColdRoomSensorNumber], DELETED.[RecordedWhen], DELETED.[Temperature], DELETED.[ValidFrom], DELETED.[ValidTo]  
 INTO [Warehouse].[ColdRoomTemperatures_Archive]  
 WHERE [ValidTo] < GetHkOldestTxTemporalTs() 

I searched a lot to see what the WITH (SNAPSHOT) option means, but I was not able to find anything.

Can you please help me in understanding what does WITH (SNAPSHOT) mean?

Let me know ,if you need any further info.

2

This is a table hint that can be used in various statements (SELECT, INSERT, DELETE, UPDATE, MERGE). See the related page at msdn: Table hints and the linked Introduction to Memory-Optimized tables.

For further information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Syntax

WITH  ( <table_hint> [ [, ]...n ] )  

<table_hint> ::=   
[ NOEXPAND ] {   
    INDEX  ( index_value [ ,...n ] )   
  | INDEX =  ( index_value )      
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]  
  | FORCESCAN  
  ...
  | SNAPSHOT
  ...

...

SNAPSHOT

Applies to: SQL Server 2014 through SQL Server 2016.

The memory-optimized table is accessed under SNAPSHOT isolation.
SNAPSHOT can only be used with memory-optimized tables (not with disk-based tables).
For more information, see Introduction to Memory-Optimized Tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.