sql-server add tag
Tom Pažourek (imported from SE)
When I try to create indexes on a temporary table in SQL Server when using the `SNAPSHOT` transaction isolation, I get this error:

> Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.

Why is it **not allowed to create indexes** on temporary tables in SQL Server when using snapshots?

I don't understand this, if I'm allowed to create temporary tables, why am I not allowed to add indexes to them?
Top Answer
david browne microsoft (imported from SE)
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.
Answer #2
NYCdotNet (imported from SE)
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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.