sp_placeobject

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

Syntax

sp_placeobject segname, objname[, partitionname]

Parameters

Examples

Usage

There are additional considerations when using sp_placeobject:
  • does not affect the location of any existing table or index data. It affects only future space allocation. This include all existing partitions in the table/index and any new partitions added later if no segment is specified for a new partition. Changing the segment used by a table or an index can spread the data among multiple segments.

  • If you use sp_placeobject with a clustered index, the table moves with the index.

  • You can specify a segment when you create a table or an index with create table or create index. You can also specify a segment at the partition level as part of a partition definition. Partitions without segment specification uses the segment specified at the table/index level. If no segment is specified for the table/index level, the data goes on the default segment.

  • When sp_placeobject splits a table or an index across more than one disk fragment, the diagnostic command dbcc displays messages about the data that resides on the fragments that were in use for storage before sp_placeobject executed. Ignore those messages.

See also alter table, dbcc in Reference Manual: Commands.

Permissions

The permission checks for sp_placeobject differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the table owner or a user with manage database privilege.

Disabled

With granular permissions disabled, you must be the database owner, table owner, or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addsegment
sp_dropsegment
sp_extendsegment
sp_helpindex
sp_helpsegment