Clustered and Non-Clustered Index : Intro, What is and Types of Index

Introduction

An index is an on-disk structure associated with a table or views that speeds retrieval of rows
from the table or view. An index contains keys built from one or more columns in the table or
view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or
rows associated with the key values quickly and efficiently.

Know : What is an Index

Index is a database object, which can be created on one or more columns. When creating the
index will read the column(s) and forms a relevant data structure to minimise the number of
data comparisons. The index will improve the performance of data retrieval and adds some
overhead on data modification such as create, delete and modify. So it depends on how much
data retrieval can be performed on table versus how much of DML (Insert, Delete and Update)
operations.

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on
table columns. For example, when you create a table and identify a particular column to be the
primary key, the Database Engine automatically creates a PRIMARY KEY constraint and index on
that column. 

Know : Types of Index

• Clustered Index
• Non-Clustered Index

Both clustered and non-clustered indexes can be unique. This means no two rows can have the
same value for the index key. Otherwise, the index is not unique and multiple rows can share the
same key value. Indexes are automatically maintained for a table or view whenever the table data
is modified.