PRB: Building Clustered Index on Empty Table Does Not Move

ID: Q96018

The information in this article applies to:

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>.

Examination of SYSINDEXES in TEST_DB will show the ROOT of the clustered index to be located in TEST_SEG2, while the FIRST entry will refer to a page within TEST_SEG1.

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.

Additional query words: segments 


Keywords          : kbprg SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 16, 1999