Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?
In modern versions of SQL Server (2014+) you can create the indexes when you create the table, e.g.:
create table #t(id int primary key, a int, index ix_a nonclustered(a))
Also you can create the temp table before the snapshot transaction starts.
Almost all DDL is prohibited within a SNAPSHOT
transaction. ALTER TABLE
and TRUNCATE TABLE
are obviously not allowable. CREATE TABLE
is whitelisted. CREATE INDEX
could be whitelisted, but simply isn't.
This is still not supported as of SQL Server 2019, but there is an open feedback forum issue for this that is worth up-voting to allow CREATE INDEX on temp tables to be allow-listed as david-browne-microsoft suggests . https://feedback.azure.com/forums/908035-sql-server/suggestions/40970308-allow-index-creation-for-tables-within-a-snapshot
In particular having this feature would be valuable for SELECT ... INTO #TempTable
scenarios where there is currently no way to add an index to the resulting temp table when in snapshot isolation without restructuring the query to create the temp table and indexes in advance.