DATABASE & SQL/PLSQL

1. Identify the tables on which the index will be created.
2. Identify the attribute on which the index will be created.
3. Identify the type of index will be created.
4. 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.)
5. Create the index.

Syntax:

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

Syntax:

sp_helpindex  table_name

CREATE NONCLUSTERED INDEX  INemp_name
ON  employee (emp_name)

sp_helpindex  employee

output