Create Indexes

You must be the owner of a table to create or drop an index. The owner of a table can create or drop an index at any time, whether or not there is data in the table. Indexes can be created on tables in another database by qualifying the table name.

Before executing create index, turn on select into.

The syntax is:
sp_dboption,'select into', true

The simplest form of create index is:

create index index_name
     on table_name (column_name) 

To create an index on the au_id column of the authors table, execute:

create index au_id_ind 
on authors(au_id) 

The index name must conform to the rules for identifiers. The column and table name specify the column you want indexed and the table that contains it.

You cannot create indexes on columns with bit, text, or image datatypes.

Note: The on segment _name extension to create index allows you to place your index on a segment that points to a specific database device or a collection of database devices. Before creating an index on a segment, see a system administrator or the database owner for a list of segments that you can use. Certain segments may already be allocated to specific tables or indexes for performance reasons or for other considerations.