Creating objects on segments

A segment is a label that points to one or more database devices.

Each database can use up to 32 segments, including the 3 segments that are created by the system (system, log segment, and default) when a database is created. Segments label space on one or more logical devices.

Tables and indexes are stored on segments. If no segment is named in the create table or create index statement, then the objects are stored on the default segment for the database. Naming a segment in either of these commands creates the object on the segment. The sp_placeobject system procedure causes all future space allocations to take place on a specified segment, so tables can span multiple segments.

A System Administrator must initialize the device with disk init, and the disk must be allocated to the database by the System Administrator or the Database Owner with create database or alter database.

Once the devices are available to the database, the database owner or object owners can create segments and place objects on the devices.

If you create a user-defined segment, you can place tables or indexes on that segment with the create table or create index commands:

create table tableA(...) on seg1
create nonclustered index myix on tableB(...) 
      on seg2

By controlling the location of critical tables, you can arrange for these tables and indexes to be spread across disks.