something like adding page numbers to book in real world example. As a
publisher if you are not adding page number to book it’s very hard for reader
or user to read book and find information from specific page.
Indexes directly affects application performance, good indexing improves application
performance and bad indexing reduce application performance.
An index is an
on-disk structure associated with a table or views that speeds retrieval of
rows from the table or view.
A table can have only one clustered index. That is because
a clustered index is the actual table sorted in order of the cluster key.
If table has no clustered index data are stored in an unordered structure called heap.
word Example for clustered index:
Phone directory book, each directory of phone book
represents one row. Primary key on table is by default clustered index for
A table can
have one or many non clustered indexes (i.e. max 999 for SQL SERVER 2008 and
249 for SQL server 2005).
index uses clustered key to sorts and stores the data rows of the table or view.The
clustered index is implemented as a B-tree index structure that supports fast
retrieval of the rows, based on their clustered index key values.
Example for Clustered Index:
In Web-shop billing
or shipping Address has multiple column data we can add User Name+MobileNumber Or telephone as Non Clustered index.
It is recommended
to use unique key for non clustered indexes.
Special purpose indexes:
With a hash
index, data is accessed through an in-memory hash table.
Memory-optimized nonclustered indexes
memory-optimized nonclustered indexes, memory consumption is a function of the
row count and the size of the index key columns
can be a property of both clustered and nonclustered indexes.This ensures
that index kay contain no duplicate values.
and persisted, representation of the XML binary large objects (BLOBs) in the
xml data type column.
index provides the ability to perform certain operations more efficiently on
spatial objects (spatial data) in a column of the geometry data type.
nonclustered index. A proper designed filtered index can improve query
performance, reduce index maintenance costs, and reduce index storage costs
compared with full-table indexes.
Index with included columns
nonclustered index that is extended to include nonkey columns in addition to
the key columns.
Index on computed columns
An index on
a column that is derived from the value of one or more other columns, or
certain deterministic inputs.