sp_addsegment System Stored Procedure

Defines a segment on a database device in the current database.

Syntax

sp_addsegment segname, logical_name

where

segname

Is the name of the new segment, which is added to the syssegments table of the current database. Segment names must be unique in each database.

logical_name

Specifies the database device where segname will be located. A database device can have more than one segment associated with it.

Remarks

The sp_addsegment system stored procedure defines segment names for database devices that have been assigned to a specific database with the ALTER DATABASE or CREATE DATABASE statement. The maximum number of segments for any database is 32. Segments are named subsets of a database defined per device. Before you can use segment procedures in a database, make the database the current one by issuing the USE database statement.

Once a segment has been defined, it can be used with the CREATE TABLE statement, the CREATE INDEX statement, and in the sp_placeobject system stored procedure, which puts data for a table or index on the segment. When a table or index is created on a particular segment, all subsequent data for the table or index is put on that segment.

To extend the range of a segment to another database device used by the same database, use the sp_extendsegment system stored procedure.

If a database is extended with the ALTER DATABASE statement on a device used by that database, the segments mapped to that device are also extended.

The SYSTEM and DEFAULT segments are mapped to each database device included in a CREATE DATABASE or ALTER DATABASE statement. The LOGSEGMENT is also mapped to each device, unless it is put on a separate device by using the LOG ON extension to the CREATE DATABASE statement or by using the sp_logdevice system stored procedure. To unmap these segments, use the sp_dropsegment system stored procedure. For more information, see the Microsoft SQL Server Administrator's Companion.

Example

This example creates a segment named INDEXES on the database device named DEV1.

sp_addsegment indexes, dev1

Permission

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

Tables Used

syssegments, master.dbo.sysusages, master.dbo.sysdevices

See Also