What
is an Index?
Indexes
of SQL Server are similar to the indexes in books. They help SQL
Server retrieve the data quicker. Index is a database object, which
can be created on one or more columns.
Types
of Indexes?
SQL
Server has two major types of indexes:
Clustered
Non-Clustered
What
is Clustered index?
A
clustered index sorts and stores the data rows of the table or view
in order based on the clustered index key. 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.
What
is Non-Clustered index?
A
non-clustered index can be defined on a table or view with a
clustered index or on a heap.
Each
index row in the non-clustered index contains the non-clustered key
value and a row locator.
This
locator points to the data row in the clustered index or heap having
the key value.
The
rows in the index are stored in the order of the index key values,
but the data rows are not guaranteed to be in any particular order
unless a clustered index is created on the table.
How
many clustered indexes there can be in one table?
Only
one.
How
many non-clustered indexes there can be in one table?
For
SQL Server 2005: 249 Non-clustered Index
For
SQL Server 2008: 999 Non-clustered Index
Disadvantages
of the Indexes?
Inserts
and updates takes longer time with clustered index.
It
takes some disk space to create Non-Clustered index
How
many columns can we include in non clustered index?
Max
16 columns can be combined to make a single composite index key, with
a cap that the max size of the combined values is 900 bytes.
Why
Use an Index?
Use
of SQL server indexes provide many facilities such as:
- Rapid access of information
- Efficient access of information
- Enforcement of uniqueness constraints
Is
Clustered index store the table data in sorted order?
Yes!
When
you create an index on a column or number of columns in MS SQL
Server, you can specify that the index on each column be either
ascending or descending.
0 comments:
Post a Comment