Sql Indexes – Clustered index – Non Clustered Index

Sql indexes may them be clustered index or non clustered index is stored as a binary tree inside a index page on the SQL server.

Clustered index:

  1. Entire rows value is stored at leaf of binary tree
  2. Only one clustered index can be built on a table or view
  3. Indexed values are either in ascending or descending order
  4. Table with cluster index in called clustered table else heap

Clustered index syntax:

CREATE CLUSTERED INDEX [IX_CLUSTERED_COMPUTER_ID] 
ON [dbo].[nics] ([computer_id] ASC)

Non Clustered Index:

  1. Index column value and pointer to the row is stored at leaf of binary tree
  2. Multiple (999 in MSSQL Server 2008) clustered indexes can be built on single table
  3. More columns can be added to be stored besides indexed column
  4. In here query engine need to perform additional task to get value of row from pointer
CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_COMPUTER_ID] 
ON [dbo].[nics] ([computer_id] ASC)

SQL indexes can be further divided as composite index and unique  index.

Composite index:

  1. Index that has more than one column
  2. E.g. in SQL server you can up to 16 columns till size is less than 900 bytes
  3. Both clustered and non clustered index can be composite

Unique index:

  1. This makes sure that value of indexed columns combined is unique
  2. Not individual columns need to be unique
  3. If you define primary or unique constraint then sql server automatically creates index
  4. Primary key
    1. When ever you define one or more columns as primary then sql server creates unique clustered index on the columns
    2. You can change auto behavior to make it non clustered
  5. Unique constraint
    1. When you apply unique constraint to any column sql server automatically creates non clustered unique index
    2. You can change auto behavior to make it clustered if table has not one already
  6. Covering index: Index containing all columns of table

 

Leave a Reply

Your email address will not be published. Required fields are marked *