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

  • How can I make the following a little more error proof where I check the Table and Schema Name?

    IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID')

    DROP INDEX QUOTE_DIMENSION.IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID

    [/code]

    IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID')

    CREATE INDEX IDX_QUOTE_DIMENSION_QD_TRANSACTION_ID ON QUOTE_DIMENSION (QD_TRANSACTION_ID)

    You thought, ideas or suggesstions would be greatly appreciated.

    🙂

    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/

  • Any reason to not use the "With Drop Existing" option on Create Index, instead of an explicit drop?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I would say to create the index if it doesn't exist. Using the DROP EXISTING implies that the index already exists. We do it here to ensure that the script is rerunnable when creating new indexes, so use it when modifying existing as well.

  • My question is why do the IF EXISTS ... DROP followed by an IN NOT EXISTS ... CREATE? Why not just do the IF EXISTS ... DROP folloed immediately by the CREATE?

  • What's wrong with using the standard "IF EXISTS"....DROP INDEX....CREATE INDEX structure used so commonly?

    Ninja'd by Lynn 😀

  • obligatory compatibility nazi post:

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

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

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

    CREATE INDEX indexname ON SCHEMA.tablename(columnlist);

    Why not simply:

    Create Index IndexName on schema.tablename (columnlist)

    with (drop_existing = on);

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (8/13/2012)


    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;

    CREATE INDEX indexname ON SCHEMA.tablename(columnlist);

    Why not simply:

    Create Index IndexName on schema.tablename (columnlist)

    with (drop_existing = on);

    Because DROP_EXISTING = ON assumes that the index already exists. From BOL:

    DROP_EXISTING

    Specifies that the named, preexisting clustered or nonclustered index should be dropped and rebuilt. The index name specified must be the same as a currently existing index. Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account.

    The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.

    If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted.

    A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause; however, a unique clustered index can be changed to a non-unique index, and vice versa.

    Note When executing a CREATE INDEX statement with the DROP_EXISTING clause, SQL Server assumes that the index is consistent, that is, there is no corruption in the index. The rows in the specified index should be sorted by the specified key referenced in the CREATE INDEX statement.

    Here is a test from my SandBox database:

    /****** Object: Table [dbo].[test] Script Date: 08/13/2012 14:41:53 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))

    DROP TABLE [dbo].[test]

    GO

    /****** Object: Table [dbo].[test] Script Date: 08/13/2012 14:41:53 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[test](

    [testid] [int] IDENTITY(1,1) NOT NULL,

    [TestVal1] [int] NULL,

    [TestVal2] [varchar](255) NULL,

    [TestVal3] [int] NULL,

    [TestVal4] [varchar](255) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    CREATE INDEX idx_test ON dbo.test(testid) WITH (drop_existing = ON );

    GO

    And the error message at the end:

    Msg 7999, Level 16, State 9, Line 2

    Could not find any index named 'idx_test' for table 'dbo.test'.

  • GSquared (8/13/2012)


    Why not simply:

    Create Index IndexName on schema.tablename (columnlist)

    with (drop_existing = on);

    Because that does not work if the index does not exist.

    Create Index doesnotexist on DBO.Test (ID)

    with (drop_existing = on);

    Msg 7999, Level 16, State 9, Line 1

    Could not find any index named 'doesnotexist' for table 'DBO.Test'.

    Create With drop_existing is equivalent to DROP INDEX ... CREATE INDEX, not IF EXISTS ... DROP INDEX ... CREATE INDEX

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If I remember correctly, the Index name is unique. Do you really need to verify both the object AND table name? Couldn't you just make sure that the index name exists and then drop it?

  • Index names are only unique per table. It's quite possible to have the same name for an index on multiple tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ah, thanks Gail!

  • Lynn Pettis (8/13/2012)


    My question is why do the IF EXISTS ... DROP followed by an IN NOT EXISTS ... CREATE? Why not just do the IF EXISTS ... DROP folloed immediately by the CREATE?

    I'm Droping the Indexes before I load the table and Creating them after the load.

    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/

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply