So I hate cats… but this describes me perfectly right now.
Error 3964 on CREATE INDEX on temporary table under SNAPSHOT ISOLATION LEVEL.
I understand the error but what I don’t understand is WHY on a # temporary table (that is tied directly to my SPID) this would be an issue?
I found this: CONNECT ITEM
and the reply from Microsoft was:
“Thanks for reporting it. Some DDL operations that are allowed inside snapshot isolation. For example, CREATE TABLE is ok because it did not exist. However, altering/creating index is not allowed. We are aware of this limitation and will look into it in the future release”
Sweet 🙂 that was back in 2010 and now the connect item has been closed “As By Design” 🙁
I’d love to give you some magical code to get around this issue but unfortunately I can’t. The most you can do with a # temp table is during the definition you can specify a primary key or a unique non-clustered index but that’s it.
Back to the drawing board 🙂
Happy Tuesday everyone!!
Enjoy!! (Follow me on Twitter: @ColinStasiuk)