Indexing in SQL Server

Question

Could somebody please explain how indexing works in SQL Server and what is a clustered and non clustered index?

When we create an index on some column how does it increase the performance and how are the values of column stored to increase the performance?

Answer 1

You can write a book about it.

You should get familiar with this

Table and Index structures

Understanding and design indexes

And then if You still don't catch something ask here for some details.

Answer 2

Check out:

• SQL Server Index Basics

• SQL Server Indexes: The Basics

for some good intro material

Answer 3

In short:

An index is faster because it is a subset of the table that is cached in memory.

Also, if all data requsted is in the index the server does not even have to query disk.

Clustered vs. unclustered:

A table can only have one clustered index as it means that the data on disk is ordered in the same order as the index. If you insert new data the table will be adjusted to keep this order.

Clustered index is faster often to search, especially if you need more data from each row but gets a performance hit on inserts.

Usually you have clustered index on the ID column.

Unclustered indexes you can have many of and they are for speeding up queries.

By combining the fields that most often are queried together and order the fields in the index so that the most varied field is first you can get big performance boosts, but read the links the other supplied, you can always öearn more about indexing, even if you build you own database =)

Answer 4

Let me try to explain the concept of an index. This explanation is not SQL Server specific, and I'm perhaps over simplifying things a bit. However, it may help you get an idea why an index is useful.

Assume you store simple records consisting of an ID and a name. Data are stored in rows where the first column (#) is a record number or an address enabling us to point to a particular row.

#  Id  Name
------------
0  1   Lorem
1  2   Ipsum
2  3   Dolor
3  5   Sit
4  6   Amet

Assume that you want to find a the row having a specific name, say "Sit". The only way you can find it is by scanning the entire table looking for that particular name. Doing that on a big table is time consuming.

To solve that problem you can create an index on the Name column. An index is like a table:

Name   #
--------
Amet   4
Dolor  2
Ipsum  1
Lorem  0
Sit    3

Note that the names are sorted which makes it easier to search for a particular name. Finding a name no longer requires a complete table scan but can instead be speeded up by searching the index. When the name is found the corresponding record number can be looked up and the associated record can be retrieved. Often the index will use a balanced tree which makes it quite efficient to search.

To sum up, search is faster if you have an index but you will have to maintain an additional data structure that consumes spaces and requires updating when the underlying table is modified.

A clustered index is a special index that defines how the table is stored. Having an clustered index on the Id column will ensure that the rows in the table are ordered by ID. You don't need an additional index data structure to maintain this index. The index is already defined by how the table is stored.