PRB: Building Clustered Index on Empty Table Does Not Move

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

One technique of moving an object onto a new segment within a database is to create a clustered index on the object. Creation of a clustered index will force a rebuild of the table, thus moving the table to whatever segment the clustered index was specified to be built on. However, creating a clustered index on a table containing no data will not result in the underlying table being rebuilt.

MORE INFORMATION

Consider the following scenario:

CREATE DATABASE TEST_DB ON TESTDEV1 = 2, TESTDEV2 = 2, TESTDEV3 = 2 go USE TEST_DB go SP_ADDSEGMENT TEST_SEG1, TESTDEV2 go SP_ADDSEGMENT TEST_SEG2, TESTDEV3 go CREATE TABLE TEST_TABLE ( COL1 INT NOT NULL ) ON TEST_SEG1 go CREATE CLUSTERED INDEX CINDEX ON TEST_TABLE( COL1 ) ON TEST_SEG2 go

SP_HELPSEGMENT will now show TEST_TABLE to be on TEST_SEG2. However, executing a DBCC CHECKALLOC against TEST_DB will result in message 2558

   Extent not within segment: Object <object ID of TEST_TABLE>, indid
   1 includes extents on allocation page <allocation page in
   TEST_SEG1> which is not in segment <segment number of TEST_SEG2>.

If one row of data is added to the table, and the clustered index is dropped and rebuilt on TEST_SEG2, the table will be moved to TEST_SEG2 and the 2558 errors corrected.

To move an empty table to a new segment, either drop and recreate the table on the desired segment, or add one row of data, build a clustered index on the table and the desired segment, and subsequently truncate the table.

Should the table already contain data, dropping and rebuilding the clustered index will cause the table to be moved to the proper segment and eliminate the 2558 errors.