5

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?

  • 4
    It gives the answer to your question right in the error itself. – Tara Kizer May 15 '18 at 18:56
  • How can adding of index lead to inconsistency? Is there some way to add the indexes when I'm creating the temp table that wouldn't lead to these inconsistencies? – Tom Pažourek May 15 '18 at 18:58
  • 1
    Why do you have to create the #temp table inside the transaction? – Aaron Bertrand May 15 '18 at 19:08
  • @AaronBertrand It's how my application is structured at the moment. It didn't occur to me, but I suppose I don't really need to create it inside the transaction. Good point. It might be better to create it outside, after all, it's good to have the transactions as short as possible. – Tom Pažourek May 15 '18 at 19:12
  • 2
    @TaraKizer: The message is absurd because it's a temp table. Snapshot doesn't need to apply to temp tables because you can't grab them from other transactions anyway. – Joshua May 15 '18 at 19:44
8

In modern versions of SQL Server (2014+) you can create the indexes when you create the table. EG

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.

  • Thanks, this looks like a good solution for me. Do you happen to know any more information about the reason for the DDL change limitations, the metadata limitations? I'd like to research that topic a bit more... – Tom Pažourek May 15 '18 at 19:16
  • 4
    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. – David Browne - Microsoft May 15 '18 at 19:38
  • 3
    And even more specifically, I think CREATE INDEX is just not whitelisted in general - the engine doesn't check to see if that is applying to a #temp table or something else. It could, potentially, whitelist CREATE INDEX ON # but not on ## or permanent. But it doesn't, today. – Aaron Bertrand May 15 '18 at 19:50
  • 1
    Yes CREATE INDEX on #temp would be simpler, as sessions see their own changes, so you wouldn't have to "fix up" the version store for the new index. – David Browne - Microsoft May 15 '18 at 19:58

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.