if exists drop index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if exists drop index

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-02-08 : 16:14:32

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

38200 Posts

Posted - 2008-02-08 : 16:21:40

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/

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-02-08 : 16:29:11

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-08 : 16:33:35

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/

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-02-08 : 16:39:17

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-08 : 16:42:18

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/

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-02-08 : 16:48:48

You're right Tara, Thank you for your help, this works perfect.

dedbeat
Starting Member

9 Posts

Posted - 2008-05-20 : 14:47:40

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 14:58:08

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

dedbeat
Starting Member

9 Posts

Posted - 2008-05-20 : 16:08:33

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?

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-20 : 20:22:02

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

dedbeat
Starting Member

9 Posts

Posted - 2008-06-02 : 11:56:09

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?

klabbas
Starting Member

1 Post

Posted - 2011-08-11 : 12:18:04

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-11 : 12:31:07

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 ;) )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources