Microsoft KB Archive/96018

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.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 , indid 1 includes extents on allocation page  which is not in segment .

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 :
 * }