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.