Microsoft KB Archive/45667

From BetaArchive Wiki
Knowledge Base

INFO: SQL Clustered and Non-Clustered Index Sort Work Area

Article ID: 45667

Article Last Modified on 2/14/2005


  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q45667


The following information discusses how the "sort work area" associated with clustered and non-clustered indexes is created and stored within SQL Server.


When a clustered index is created on a table that already contains data, a new copy of the table's data is created in the same database. When the new copy is completed, the old copy is deallocated and the "sort work area" becomes the new table. As a result, when a clustered index is created, there must be enough unreserved space in the database to hold a second entire copy of the table.

When a non-clustered index is created, data space in the TEMPDB database is used as the "sort work area." The area in TEMPDB is released when the sort is completed.

In both cases SQL Server will NOT allocate temporary storage from free disk space.

Keywords: kbinfo kbother KB45667