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.
Tables and indexes are stored on segments. If you execute create table or create index without specifying a segment, the objects are stored on the default segment for the database. Naming a segment in either of these commands creates the object on that segment. You can use the sp_placeobject system procedure to assign 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 allocate the device to the database. Alternatively, the database owner can do this using 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.
When you create a user-defined segment, you can place tables, indexes, and partitions on that segment using the create table or create index commands:
create table tableA(...) on seg1
create nonclustered index myix on tableB(...)
on seg2
This example creates the table fictionsales, which is partitioned by range according to values in the date column:
create table fictionsales
(store_id int not null,
order_num int not null,
date datetime not null)
partition by range (date)
(q1 values <= ("3/31/2005") on seg1,
q2 values <= ("6/30/2005") on seg2,
q3 values <= ("9/30/2005") on seg3,
q4 values <= ("12/31/2005") on seg4)
By controlling the location of critical tables, you can arrange for these tables and indexes to be spread across disks.
