To place a new object on a segment, first create the new segment. You may also want to change the scope of this segment (or other segments) so that it points only to the desired database devices. When you add a new database device to a database, it is automatically added to the scope of the default and system segments.
After you have defined the segment in the current database, use create table or create index with the optional on segment_name clause to create the object on the segment. The syntax is:
create table table_name (col_name datatype ... ) [on segment_name]
create [ clustered | nonclustered ] index index_name on table_name(col_name) [on segment_name]
Clustered indexes, where the bottom leaf, or leaf level,
of the index contains the actual data, are by definition on the
same segment as the table. See “Creating clustered indexes on segments”.
Figure 8-3 summarizes the sequence of Transact-SQL commands used to create tables and indexes on specific physical disks on a server using 2K logical page size.
Figure 8-3: Creating objects on specific devices using segments
Start by using the master database.
Initialize the physical disks.
Allocate the new database devices to a database.
Change to the mydata database using the use database command.
Create two new segments, each of which points to one of the new devices.
Reduce the scope of the default and system segments so that they do not point to the new devices.
Create the objects, giving the new segment names.
