sp_placeobject System Stored Procedure

Puts future space allocations for a table or index on a particular segment.

Syntax

sp_placeobject segname, objname

where

segname

Specifies the segment where the table or index is to be located.

objname

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

Remarks

You cannot change the location of future space allocations for system tables.

Putting a table or index on a particular segment does not affect the location of any existing table or index data. It affects only future space allocation. By changing the segment used by a table or index, the data can be spread across multiple segments.

If a table or index is created with the CREATE TABLE or CREATE INDEX statement, you can specify a segment. If no segment is specified, the data will go on the DEFAULT segment.

When sp_placeobject is used to split a table or index across more than one disk fragment, the diagnostic DBCC statement reports on the data located on the fragments in use for storage before sp_placeobject was executed. These messages can be ignored.

A table with text or image columns has an additional entry in the sysindexes table for the text chain, with the name column set to the name of the table preceded by the letter t and an indid 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.

Example

This example causes all subsequent space allocation for the index named employee_nc on the table employee to be placed on the segment named INDEXES.

sp_placeobject indexes, 'employee.employee_nc'
EXEC sp_placeobject textseg, 'mytab.tmytab'

Permission

Execute permission defaults to the table owner, database owner, and system administrator.

Tables Used

sysindexes, sysobjects, syssegments

See Also