Databases Reference
In-Depth Information
We could like to compare indexes to an English dictionary (this is really a widely used analogy
for indexes). Suppose we are searching for the meaning of the word "Treasure", we are sure
we have to search for it on the pages where all the words starting with "Tr" are listed. Once we
come across words starting with "Tr", we don't need to search up to the end of the dictionary.
For instance, if the words starting with "Tr" are listed from page number 725 to 729, we have
to look at only those five pages. If we don't find "Treasure" within them, we will not find the
meaning of that word in that dictionary.
Indexes are stored in the form of a B-Tree in SQL Server. Considering each index page as one
node, the top-most node is called root and the bottom-most nodes in Index B-Tree are called
leaf nodes. Any node between the root and a leaf node is called an intermediate node.
The leaf node contains the actual data pages of a database table. The root node and the
intermediate node could have Key Value(s), and pointers to another intermediate node,
downwards from the current node.
The following image shows the basic structure of an index:
contain key value+pointer to
Intermediate Page
root page
contain Keyvalue+pointer to
LEAF pages
Intermediate Page
Intermediate Page
L
E
A
F
L
E
A
F
L
E
A
F
L
E
A
F
L
E
A
F
L
E
A
F
L
E
A
F
contain actual data row
Increasing performance by creating a
clustered index
Any RDBMS supports the functionality to perform the
INSERT
,
UPDATE
, and
DELETE
operations, and retrieve the data with the
SELECT
statement. As time passes by, data will
increase in the database, and it will start creating an issue of slow retrieval of data whenever
the
SELECT
statement is processed.
RDBMS is supposed to support a very large-scale database, especially when you are talking
about SQL Server 2012. So, how can we eliminate this slow performance issue? Well, this is
when index makes an entry into the life of a database administrator!
Search WWH ::
Custom Search