Adding Objects to Another Segment

The sp_placeobject system procedure causes all future disk allocation for the specified object to occur on a particular segment. The sp_placeobject system procedure does not remove objects from their allocated segments, nor does it move an object from one database device to another. Whatever pages have been allocated on the first database device remain allocated; whatever data was written to the first database device remain on that database device.

Do not use the sp_placeobject system procedure to move indexes, because splitting indexes across database devices affects system performance. Instead, drop the index and re-create it on the new segment.

A clustered index (in which the bottom level of the index contains the actual data) is on the same segment as a table. You can move a table by dropping its index and then creating a clustered index for the table on the desired segment.

    To place an object on another segment

sp_placeobject segname, objname

where

segname

Specifies the segment to use for future space allocation for the database object.

objname

Specifies the table or index whose subsequent space allocation is to be put on the segname segment.

For example, to allocate additional space for the mytab table on bigseg, type:

sp_placeobject bigseg, mytab

Note Executing DBCC CHECKALLOC prints warning message 2558 for each object that is split across segments. You can ignore these messages if you intentionally want objects to span segments.

When you create a table with text or image columns, the data is stored on a separate chain of text pages. A table that contains text or image columns has an additional entry in sysindexes for the text chain, with the name column set to the name of the table preceded by the letter "t" and an index ID of 255. You can use sp_placeobject to store the text chain on a separate device, giving both the table name and the name of the text chain from sysindexes. For example:

sp_placeobject textseg, 'mytab.tmytab'

By default, a chain of text pages is placed on the same segment as its table. After you run sp_placeobject, whatever pages were previously written to the old device remain allocated, but all new allocations take place on the new segment.

For more information about sp_placeobject, see the Microsoft SQL Server Transact-SQL Reference.