Creating Database Objects on Segments

After you add a segment, you can explicitly place database objects on that segment. The CREATE TABLE and the CREATE INDEX statements use the ON segment_name clause to create objects on a specific segment. If you do not use the ON segment_name clause, the object is created on the default segment.

    To create a table on a specified segment

CREATE TABLE table_name (column_name datatype
    [, column_name datatype...]) ON segment_name

where

table_name
Is the name of the table.
column_name
Is a column name in the table.
datatype
Specifies the datatype of the column. System or user-defined datatypes are accepted.
segment_name
Identifies the segment to create the table on.
    To create the index for a table on a segment

CREATE [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column_name [, column_name...]) ON segment_name

where

CLUSTERED | NONCLUSTERED
Specifies whether the index is clustered or nonclustered.

CLUSTERED means that the physical order of rows on the database device is the same as the indexed order of the rows. NONCLUSTERED means that there is a level of indirection between the index structure and the data itself. For more information about indexes, see the Microsoft SQL Server Transact-SQL Reference.

A clustered index, where the bottom, or leaf, level of the index contains the actual data, resides on the same segment as the table. If you create a table on one segment and then create its clustered index on a different segment, the table travels with its index to the new segment. If you create a clustered index without specifying a segment name, the entire table moves to the DEFAULT segment. A nonclustered index can reside on a different segment from the table.

index_name
Specifies the index to add to the segment.
table_name
Identifies the table containing the index to add to the segment.
column_name
Identifies the columns in the table.
segment_name
Is the name of the segment on which you want the index created.

For example, to place the mytable table on the segment seg_mydisk1 and its nonclustered index on segment seg_mydisk2, type:

CREATE TABLE mytable (c1 varchar(30), c2 char(15), c3 datetime) 
    ON seg_mydisk1 

CREATE NONCLUSTERED INDEX my_index ON mytable (c1, c2) ON seg_mydisk2 

For the full syntax of the CREATE TABLE and CREATE INDEX statements, see the Microsoft SQL Server Transact-SQL Reference.