IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  August 13, 2012 at 4:08 pm

  #1525322

  Lowell (8/13/2012)

  ---

  obligatory compatibility nazi post:

  sysindexes should be replaced with sys.indexes, as it's going to be dropped in some future version.

  Good point, thanks.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  August 13, 2012 at 4:09 pm

  #1525323

  Lynn Pettis (8/13/2012)

  ---

  This is how I would code it. Also note that I am using sys.indexes, not sysindexes.

  IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')

  DROP INDEX indexname ON SCHEMA.tablename;

  GO

  CREATE INDEX indexname ON SCHEMA.tablename(columnlist);

  GO

  Edit: Added batch separators just to be safe. Can't remember if the CREATE INDEX has to be first in a batch and quick look in BOL didn't exactly answer the question. Will need to dig a bit more to be sure.

  Thanks for the post.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  August 13, 2012 at 5:02 pm

  #1525336

  GilaMonster (8/13/2012)

  ---

  As for the original question...

  IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Index name here' AND object_id = OBJECT_ID('Table Name Here')

  DROP INDEX ....

  CREATE INDEX ...

  Thank you Gail.:-)

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  August 13, 2012 at 10:56 pm

  #1525364

  Dropping an index by name might not work when there is an index on the same column(s) under a slightly different name.

  Proper solution should include comparing entries in sysindexkeys to desired set of columns.

  If there is an indid having the same set of columns in the same order as in the index you are about to create then you need to find index name by indid and drop that index.

  And of course, you need to check for an index with the same name on the same table.

  But if it exists but has different definitions than you better change the new index name rather than drop the index which is probably very important for some queries.

  _____________
  Code for TallyGenerator

• DQ Ninh

  SSC Rookie

  Points: 49

  More actions

  March 6, 2014 at 9:57 am

  #1694789

  Why not simply:

  Create Index IndexName on schema.tablename (columnlist)

  with (drop_existing = on);

  [/quote]

  Because sometime you may want to create an index only if it does not exist. The with drop_existing is nice, but why spend 1 hour to re-create an index on a millions of records?