Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: Alter Index ... Rebuild

    If the index to be rebuilt is a clustered index, will all non-clustered indexes be rebuilt also by rebuilding only the clustered index.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    yes they will.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I asked because even though the clustered idx was rebuilt, the fragmentation still seemed high on the non-clustered idxs, until I rebuilt them, then the fragmentation dropped.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    How did you rebuild it ... DROP/CREATE or DBCC DBREINDEX?

    From BOL:

    Syntax

    DBCC DBREINDEX
    ( [ 'database.owner.table_name'
    [ , index_name
    [ , fillfactor ]
    ]
    ]
    ) [ WITH NO_INFOMSGS ]
    Remarks

    DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means an index can be rebuilt without knowing the table's structure or constraints, which could occur after a bulk copy of data into the table.
    If either index_name or fillfactor is specified, all preceding parameters must also be specified.
    DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic. Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements.
    DBCC DBREINDEX is not supported for use on system tables.
    Result Sets

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Alter Index indexname on tablename rebuild. I wrote a procedure to look at the fragmentation, and rebuild the idx if the frag is above a certain threshhold. It checks to see if the idx is clustered, and if it is, does not rebuild the others (since they should be rebuilt when the clustered idx is rebuilt).
    Last edited by PMASchmed; 05-01-08 at 16:23.

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    From 2005 BOL:


    REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ] Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. (Emphasis added).
    If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tomh53
    From 2005 BOL:
    Ah, didn't see that bit of highlighted text where I looked, danke.

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Bitte - glad to help.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •