Post reply

Dropping and re-creating indexes

  • November 1, 2004 at 4:08 am

    #163613

    Hi

    If anyone can perhaps give some tips on dropping and re-creating indexes I woul appreciate it a lot. Is there a specific sequence in which one should drop and re-create the different types indexes?

    Thanks

     

     

  • November 1, 2004 at 5:34 am

    #528481

    I think it is non-clustered and then clustered.  I honestly get confused on this one too though.



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • November 2, 2004 at 2:39 am

    #528581

    SQL Server automatically rebuilds existing nonclustered indexes when any of the following occurs:

    -An existing clustered index is dropped using the DROP INDEX statement.

    -A clustered index is created.

    -The DROP_EXISTING option is used to change a clustered index column definition.

    Because of the above, it probably makes no difference at all as how you would run the sequence. I always make a habit of creating CLUSTERED, followed by NONCLUSTERED when creating new table. It is a practice and I am sure someone out there will dispute me on this.

     

  • November 2, 2004 at 2:56 am

    #528584

    When you think some time about it, there is actually only one way to do this efficiently.

    Remember that the leaf level of a nonclustered index contains the RID when there is no clustered index on that table, and the clustered index key when there is a clustered index.

    Now when you first create the nonclustered and then build the clustered, all nonclustered indexes will have to be recreated internally to replace the RID with the clustered index keys. Now while it is true that this is done automatically and therefore might yield no difference in the end, it might take a substantial amount of time when you do this on a large table.

    Here' s an excerpt from BOL (Creating an Index)

    ...

    If a clustered index is created on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Likewise, if a clustered index is deleted on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables.

    The preferred way to build indexes on large tables is to start with the clustered index and then build the nonclustered indexes. When dropping all indexes, drop the nonclustered indexes first and the clustered index last. That way, no indexes need to be rebuilt.

    ...

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 2, 2004 at 5:02 am

    #528595

    Thanks for the input guys, will try it and let you know

Viewing 5 posts - 1 through 4 (of 4 total)

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