locked
tempdb and Index Creation RRS feed

  • Question

  • Here is an excerpt from BOL http://msdn.microsoft.com/en-us/library/ms188281.aspx

    When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

    • If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. These include the data rows of the table. There must be sufficient free space in the destination filegroup to store the final index structure. This includes the data rows of the table and the index B-tree. You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

    • If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. This includes the index structure. The continuity of the table and index extents may be improved if more free space is available.

    When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

    • If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index, typically the clustered index, and sufficient free space in the destination filegroup to store the final structures of all the indexes. This includes the clustered index that contains the data rows of the table.

    • If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. This includes the structures of all the indexes. The continuity of the table and index extents may be improved if more free space is available.

    My question here is, why would option 1 and 2 have differing space requirement, when BOL says

    " Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified " here http://msdn.microsoft.com/en-us/library/ms188388(SQL.90).aspx

     



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, June 28, 2010 8:57 AM

Answers

  • Rebuild is not the same thing as create.

    When you *create* a clustered index, you convert the table from a heap table to a clustered table. That means that all non-clustered indexes need to be re-created (since for a heap then point to the ROD and for a clustered table, they point to the clustering key).

    When you *rebuild* a clustered index, you don't convert the table - the table was a clustered table and will continue to be a clustered table. This means that the non-clustered indexes doesn't *need* to be rebuilt.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by SQLEnthusiast Tuesday, June 29, 2010 9:23 AM
    Tuesday, June 29, 2010 9:06 AM