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

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

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

MORE INFORMATION

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.