- Identify the tables on which the index will be created.
- Identify the attribute on which the index will be created.
- Identify the type of index will be created.
- Identify the name of the index to be created. (Prefix an 'idx' to the name of the index to help in locating/identifying the index.)
- Create the index.
CREATE [UNIQUE] [CLUSTERED or UNCLUSTERED] INDEX index_name ON table_name (column_name...)
where, UNIQUE creates an index in which each row should contain a different index value.
CLUSTERED specifies a clustered index in which data is sorted on the index attribute.
NONCLUSTERED specifies a nonclustered index that organizes data only logically. the data is not sorted physically.
index_name specifies the name of the index.
The index name should be unique in the table, but it may not be unique within a database.
table_name specifies the name of table and column_name is the name of columns on which the index would be created.
CREATE CLUSTERED INDEX ad_name ON employee (emp_id)
How to Verify that Indexes has been created
sp_helpindex table_name CREATE NONCLUSTERED INDEX INemp_name ON employee (emp_name) sp_helpindex employee