This example creates a clustered index, without specifying the segment name, using the same table you just created on the new_space segment in the preceding example. Check new_space after create index to verify that no objects remain on the segment:
/* Don’t try this at home */ create clustered index mytabl_cix on mytabl(c1)
sp_helpsegment new_space
segment name status ------- ------------------------------ ------ 3 new_space 0 device size free_pages ---------------------- -------------- ----------- newdevice 3.0MB 1528 total_size total_pages free_pages used_pages --------------- ----------- ----------- ----------- 3.0MB 1536 1528 8
If you have placed a table on a segment, and you need to create a clustered index, use the on segment_name clause, or the table will migrate to the default segment.
