SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 if exists drop index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smorty44
Yak Posting Veteran

93 Posts

Posted - 02/08/2008 :  16:14:32  Show Profile  Reply with Quote
I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.

syntax:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no
go
create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go

Error when index does not exist:
Server: Msg 3703, Level 11, State 7, Line 1
Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.

Is there anyway to not get this error?

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/08/2008 :  16:21:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Get rid of the first GO statement as you are now outside of the IF block.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

smorty44
Yak Posting Veteran

93 Posts

Posted - 02/08/2008 :  16:29:11  Show Profile  Reply with Quote
Ok, Thank you, I have removed the first GO however, if the index does not exist then I still get the error. Is there anyway to prevent the error? Is there a statement that will ignore the drop index if it does not exist?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/08/2008 :  16:33:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
The script that you posted in this thread says IF EXISTS/CREATE INDEX. Please post exactly the script that is getting the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

smorty44
Yak Posting Veteran

93 Posts

Posted - 02/08/2008 :  16:39:17  Show Profile  Reply with Quote
This is the script I am using.

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no
create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go

This script will drop then create the index, however, if the index does not exist to begin with then I get the error: Server: Msg 3703, Level 11, State 7, Line 1
Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.

I have to use this script when we restore from a client db to our own db and I need this to create the indexes we need.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 02/08/2008 :  16:42:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't understand why you would drop an index and then immediately recreate the same exact one. Just use this instead:


IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no')
	create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

smorty44
Yak Posting Veteran

93 Posts

Posted - 02/08/2008 :  16:48:48  Show Profile  Reply with Quote
You're right Tara, Thank you for your help, this works perfect.
Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 05/20/2008 :  14:47:40  Show Profile  Reply with Quote
Hey Tara,

I'm actually inserting data into a rather large table through a sproc. When this table was created indexes were not created on the table. And this sproc will run as a weekly job to update data.

what i'm trying to do is if exists (indices) drop...
perform update
add index.

how would i go about writing out the drop indices if exists? also would you recommend this to be the best way? theres 1 clustered and about 12 non clustered indexes that i plan to create.

look forward to your response. thanks!

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/20/2008 :  14:58:08  Show Profile  Reply with Quote
quote:
Originally posted by dedbeat

Hey Tara,

I'm actually inserting data into a rather large table through a sproc. When this table was created indexes were not created on the table. And this sproc will run as a weekly job to update data.

what i'm trying to do is if exists (indices) drop...
perform update
add index.

how would i go about writing out the drop indices if exists? also would you recommend this to be the best way? theres 1 clustered and about 12 non clustered indexes that i plan to create.

look forward to your response. thanks!




Firstly you should consider how will your table be used. Will inserts happen frequently or is it that most time you will retrieving data from the table. If lots of indexes exists in a table to which inserts occur frequently, then performance might be a problem as each time it has update all of the indexes.On the other hand, If your table is used for frequent retrieval, then adding indexes on right columns can boost the performance.

the syntax for dropping will be

IF EXISTS (SELECT name FROM sysindexes WHERE name = yourindexname) DROP INDEX yourindexname
Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 05/20/2008 :  16:08:33  Show Profile  Reply with Quote
thanks a lot for the quick response.
my table contains almost 2 million records and grow. insertions are made once a week or once in 2 weeks via a job. data is also retrieved frequently. i need the indexes for quick data query. so to increase performance when updating i want to drop my indexes, perform the update and recreate the indexes.
any good ideas for the best way of approach?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/20/2008 :  20:22:02  Show Profile  Reply with Quote
DBCC REINDEX?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 06/02/2008 :  11:56:09  Show Profile  Reply with Quote
so im dropping an index via a sproc. the sproc is run on a seperate database than the table which gets the index dropped.
so its
drop index (index name) ON (database).dbo.(tablename)

how can i write an if exists into this?

if i write if exists (select name from sysindexes where name = (indexname)
it will check sysindexes in the current database (which the proc is running on) and i want it to check on another databases sysindexes...is there anyway to do this? or maybe theres a different approach?
Go to Top of Page

klabbas
Starting Member

USA
1 Posts

Posted - 08/11/2011 :  12:18:04  Show Profile  Reply with Quote
I know this is an old posting, but since this came up in the search engine, I thought I'd help reduce someone's search time later...

The biggest problem I see with most of the above postings is that they do not address a "multiple table - same index name" situation. In all of the above examples, any table with that index name will show it as "existing."

You need to be sure to include your table name with your index check. I have had success with the following (I was looking to see if there were better practices when I was searching):

IF EXISTS (
   SELECT 'X'
   FROM sysindexes
   WHERE id = (SELECT OBJECT_ID('accounts'))
     AND name = 'idx_acct_no'
)
   DROP INDEX accounts.idx_acct_no


For the person trying to do this through a stored proc in a different database, consider using sp_executesql, and start it with a "USE [DatabaseName]" statement.

You may also want to consider creating a table, inserting the results of EXEC sp_HelpIndex 'dbo.accounts', then querying the resulting table. (This is usually overkill for me, though).



Kurt
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/11/2011 :  12:31:07  Show Profile  Reply with Quote
Instead of dropping and recreating I'd suggest a better pattern is to Disable and Rebuild. For example:
--Set up table and index
CREATE TABLE dbo.Foo (ID INT)

CREATE INDEX IX_Foo_ID ON dbo.Foo (ID)

-- Begin processing
ALTER INDEX IX_Foo_ID ON dbo.Foo DISABLE

-- Insert Data

ALTER INDEX IX_Foo_ID ON dbo.Foo REBUILD
The advantage is:
1. You don't have to check to see if the index exists or not.
2. If there is a failure you don't have to figure out which indexes were not created, only which ones are still disabled (which is much easier to query for objects that still exists ;) )

Edited by - Lamprey on 08/11/2011 12:32:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000