Checking for indexes in tempdb in SQL Server
Posted on 2016-09-15
I'm working in C# on SQL Server 2008 and above databases.
In my code I create an index on a temp table:
create unique clustered index iID on #Subscriptions (ID,PRODUCT_CODE)
Before I create the index however I also have a check to see if the index already exists and if it does to drop it:
IF EXISTS (SELECT 1 FROM tempdb..sysindexes WHERE name = 'iID') drop index #Subscriptions.iID
However I am getting an error where the EXISTS check works, but when it goes to drop the index it says:
Additional information: Cannot drop the index '#Subscriptions.iID', because it does not exist or you do not have permission.
At the moment I have this in a try/catch block to ignore the error, and I find I can successfully create the new index after the error, so I doubt that it is a lack of permission, so can anyone explain what is going on here?