Index

INDEX:

Indexes are used by queries to find data from tables quickly.

We can create indexes on tables or views .

create index indexname 
on table or viewname (columns)

ex:

create index IX_GetempSalary
on emp (sal asc)

CLUSTERED INDEX :

Clustered index determines the physical order of data on a table ,for this reason a table have only one clustered index.

If we create a primary key by default clustered index will be created.

CREATE clustered index indexname
on tablename (columns)

ex :

create clustered index Ix_ch1Id
on ch1(id)

NON CLUSTERED INDEX :

Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

If we create unique key on table , by default non clustered index will be created.

CREATE clustered index indexname
on tablename (columns)

ex :

create nonclustered index Ix_ch1Name
on ch1 (name)

UNIQUE INDEX :

Unique index enforces the umiqueness of the key values in the index.

By default primary key constraint creates a unique clustered index.

Uniqueness is a property of an index ,and both clustered and non clustered indexes can be unique.