An index is an internal table structure that SQL
Server uses to provide quick access to rows of a table based on the values of
one or more columns. Indexes in SQL Server are like the indexes at the back of
a book, which help in locating content.
Advantages
of Using Indexes
The primary purpose of an index is to provide faster
access to data pages. Rather than scanning each page for the required data, the
server will scan the index, get the address of the data storage location, and
directly access the information. Indexes are also used as a mechanism of
enforcing data uniqueness. Creating a unique index will force unique values
into the columns. An index speeds up the processing of queries that use join or
other clauses, like ORDER BY or GROUP BY, by allowing faster access to data. An
indexes are said to have the following advantages:
·
Improve the speed of the execution of
queries.
·
Enforce uniqueness of data.
·
Speed up joins between tables.
Disadvantages
of Using Indexes
In effectively designed, indexes improve the
performance of queries. But it is not wise to index every column of table. You need
to consider a few parameters while creating indexes, like:
·
It takes time to create an index.
·
Each index created requires space to
store data along with the original data source- the table.
·
An index get updated each time the data
is modified.
Types
of Indexes
There are two types of indexes
·
Clustered Index
·
Non Clustered Index
Clustered
Index
In a clustered Index:
·
The data is physically sorted.
·
Only one clustered index can be created
per table, so you should build it on attributes that have a high percentage of
unique and are not modified often.
How
Clustered Indexes Work
In a cluster index, data is sorted at the level of
the B-tree. The data page of a table are like folders stored in an alphabetical
order in the filing, and the rows of data are like the document stored in
folders.
NonClustered
Index
In
nonclustered index:
·
The physical order of the rows is not
the same as the index order.
·
Nonclustered indexes are typically
created on columns used in joins and WHERE clauses, and whose values may be
modified frequently.
·
SQL Server creates nonclustered index by
default when the CREATE INDEX command is given
·
There can be as many as 259 (SQL Server
2005) nonclustered indexes per table.
How
Clustered Indexes Work
SQL Server creates a nonclustered index by default.
The data is present in a random order, but the logical ordering is specified by
the index. The data rows may be randomly spread throughout a table.The
nonclustered index tree contains key in a sorted order, with the leaf level of
the index containing the pointer to the data page and the row number in the
data page.
Indexes
and Heap Structures
SQL Server supports indexes defined on any column in
a table, including computer columns. If a table does not have any clustered
index, data is not sorted in a particular order. This structure is called a
heap
Each table in SQL Server is allocated one
extent(eight contiguous 8KB pages) in the database file. When this one extent
is filled with data, another is allocated to the table. However, these extents
are not placed physically next to each other, they are scattered in the
database file. Therefore, data access on a heap is slow as compared to tables
have been indexed.
Features
of Indexes
·
Indexes accelerate queries that join
tables and perform sorting and grouping.
·
Indexes can be used to enforce
uniqueness of rows.
·
Indexes are useful on columns in which
the majority of data is unique. An index on column containing a large amount of
duplicate data is not useful.
·
When you modify the data of an indexed
column, the associated index are automatically updates.
·
You require time and resources to maintain
indexes. You should not create an index that is not used frequently.
·
A clustered index should be created
before a nonclustered index. A clustered index changes the order of rows. A
nonclustered index would need to be rebuilt before a clusterd index
·
Typically, nonclustered indexes are
created on foreign keys
Guidelines
to Create an Index
·
Identify the tables on which the index
will be created.
·
Indentify the attribute on which the
index will be created.
·
Identify the type of index to be created
·
Identify the name of the index to be
created (Prefix an ‘idx’ to the name of the index to help in identifying the
index)
