I'm using SET TRANSACTION ISOLATION LEVEL SNAPSHOT with snapshot enabled on the database but what I'm noticing is that if I do a simple delete statement such as
BEGIN TRANSACTION
DELETE * FROM TableA
INSERT INTO TableA(...) SELECT (...) FROM TableB
COMMIT TRANSACTION
Effectively wiping out the table clean and re-populating it with new data as data is loaded in from another table. Whilst this operation is happening, doing a simple select which also runs in a transaction with snapshot declared, for example
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT ID FROM TableA WHERE SomeColumn = 'A'
Does a scan on the table if the DELETE / INSERT statements are running otherwise an index seek. Is that expected behaviour?
