Solved

Checking for indexes in tempdb in SQL Server

Posted on 2016-09-15
21
30 Views
Last Modified: 2016-09-16
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?
0
Comment
Question by:purplesoup
  • 7
  • 5
  • 4
  • +2
21 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
You're only checking for the index name but you should include also the table so you need to be sure it's the same index in the same table:
IF EXISTS (
        SELECT 1 FROM tempdb..sysindexes 
        WHERE id = OBJECT_ID('#Subscriptions')
             AND name = 'iID') 
    drop index #Subscriptions.iID

Open in new window

1
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Hi,

Please use below

--

IF OBJECT_ID('tempdb..#Subscriptions') IS NOT NULL
    DROP TABLE #Subscriptions

--

Open in new window

1
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
If you are using temp table then always drop temp table each time rather then the index.
1
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
When you're working on a temp table, then there should be no need to query this information. It should be already known in your process.

So the question is: What are you doing? Why do you need to create a index on a temp table out of process order?
1
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Don't create the index unique. Only constraints have to have unique names per database, not simple indexes. Create it like:

create index iID on #Subscriptions  (ID,PRODUCT_CODE);
1
 

Author Closing Comment

by:purplesoup
Comment Utility
Thanks that looks like it was what I was missing.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
But that doesn't make any sense. How can you have the same index on a temp table twice?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
It makes a lot of sense since tempdb stores objects by session so you can have the same name many times. You are only not able to see it because you can't access others session and that's why the filter by the object_id is important here.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
But you drop the index on the very table that is in your session only to create it back? Because you can't drop indexes on temp tables in other sessions, can you? How does that make sense?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
The only scenario that I can think of is this:

The asker builds a stored procedure where he wants to create a temp table with a specifically named uinique contraint/index and names of unique constraints have to be unique per database!

Now the procedure will be used by multiple users in parallel so the problem is when you try to create that UNIQUE index with the name iID. That will FAIL on any subsequent attempt after the first procedure execution. SO the idea would be to AVOID CREATING an constraint index with the same name rather than dropping it as you can't drop the constrain on other sessions, that is the parallel procedure execution.

So that would not work unless you come up with a random guaranteed unique name in a dynamic construct or if you create th index, as I suggested, simple with no constraints of any kind because in that case there will be no name conflict. You can have the same index name on multiple tables.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
In tempdb the objects are dropped after you close the session so the OP's process is only for when he needs to run it more than once in the same session so he needs to be sure that the index isn't already created.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
But you can have situations when 2 procedure executions happen at the same time and the temp table and its constraints already exist so the second one will fail! If we are talking of the same session why would you want to create same constraint twice?

Your code simply checks if the index on the same table and same session exists and if yes it drops it only to create it back , because that is the scenario as described in the question. That doesn't make sense to me!
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Agree with Zberteoc. I have never faced this kind of scenario where I have drop an index from a temp table.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
But you can have situations when 2 procedure executions happen at the same time and the temp table and its constraints already exist so the second one will fail!
I guess it's here where you're failing and that's why is making confusion to you. That can NEVER have in tempdb unless the OP is using global temporary objects (##tableName) and that's not the case.

I have never faced this kind of scenario where I have drop an index from a temp table.
Sorry to say this but you're limiting your knowledge to your own experience. One of the things that makes me  keep here in EE is to try to solve situations that I'll never pass in my professional life because different people use different solutions. And it's always good to know what people are doing around the world so we can get some knowledge from their also.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Ok so if you are in the same session then he is checking whether index is there or not ? My point is why only check for index .. we can directly check the table and if table is not there , we can create the temp table and index on it.

What extra benefit we will get by just checking the index ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
What extra benefit we will get by just checking the index ?
That really depends on the strategy used by OP for this. We'll need a global view of the algorithm to give a better alternative. I only gave the solution for this very small part of his code.
0
 

Author Comment

by:purplesoup
Comment Utility
As some background I'm actually re-coding something in C# from an old applicaton that is about 20 years old, so I didn't design how it works, this was what is was doing. It may be there is some connection pooling going on or something as at the moment I'm just running tests against my code, and sometimes a test passes and that error never happens, other times it will flag that the index exists and I get the error when I couldn't delete because it then couldn't find it.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Without knowing your exact context:

Just "recoding" is a useless task, when you don't correct errors. And from a process view point, your step of testing whether an index exists on a temporary table has a pretty strong code smell.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
If you run this in 2 separate query windows:
drop table #temp
create table #temp (col varchar(10) CONSTRAINT unq_col UNIQUE (col))

Open in new window

will fail with:
Msg 2714, Level 16, State 5, Line 5
There is already an object named 'unq_col' in the database.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint. See previous errors.

or if you run this in 2 separate query windows:
 
drop table #temp
create table #temp (col varchar(10) CONSTRAINT PK_#temp PRIMARY KEY CLUSTERED (col))

Open in new window

will fail with:

Msg 2714, Level 16, State 5, Line 6
There is already an object named 'PK_#temp' in the database.
Msg 1750, Level 16, State 0, Line 6
Could not create constraint. See previous errors.

That is what I was talking about! But to create an index as tried in the question in 2 separate query windows:
drop table #temp
create table #temp (col varchar(10))
create unique clustered index unq_col on #temp  (col)

Open in new window

actually does NOT create a constraint, as I initially thought, but a simple index called iID, which can exist with the same name in multiple tables even in the same session, so no errors here!

This means that it does NOT make sense to check an index existence before you created it unless you try to create it twice on the same temp table in the same session, which again makes no sense, or unless it is a PK or defined with a constraint but that to avoid name conflict over multiple sessions!
1
 

Author Comment

by:purplesoup
Comment Utility
Well all I can say is that when I ran tests on my code, sometimes the tests got the error I was describing, and the solution suggested fixed the problem.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Than the only explanation is that you are trying to create that index twice on the same table.
1

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now