Introduction
SQL is a database language, which allows you to define, manipulate and retrieve the data from the relational databases. If you are new to SQL, you can read SQL For Beginners series of articles.
SQL Indexes does the same thing as real world indexes present in the books. You want to search a particular topic in a book and don't want to waste your precious time. What will you do? Turn each page and traverse in a sequence or open the index, search for that topic and directly go to that page? I guess most of us will go with the second method of searching the index. In the similar way, suppose we want to search some data in our database , we can simply create an index.
SQL Indexes allows a quick access to the data. If we don't use an index, when we have a huge amount of data in order to search a record, each row will be scanned in the table and this affects the overall performance of the Application. Hence, the use of an index is encouraged in such a scenario.
Types of Indexes
There are two types of index:
- Clustered Index
- Non-clustered Index
Clustered Index
A clustered index is an index in which the data is sorted and stored, based on the key values. If a clustered index is applied on the table, the data is physically sorted and stored. There can be only one clustered index on a particular table.
Hence, a clustered index should be applied on a column, having unique values and much less modification is done on these values. Usually, it is applied to the primary key column of the table.
Data is stored at the leaf level of the B-tree in a clustered index.
Non-clustered Index
A non-clustered index is similar to clustered index but the data is not physically sorted. There can be up to 999 non-clustered indexes on a table and it can be also be applied to a column, where the value is frequently changing. When you give Create Index statement, SQL Server creates a non-clustered index by default. The data is present in a random order but the logical ordering is specified by the index.
Creating Indexes
- CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX IndexName ON table_or_view_name (column_name(s))
where,
Unique creates an index, where an each row contains a different index value.
Clustered specifies that the index should be clustered index.
Non clustered specifies that the index should be non-clustered index.
Table_or_view_name specifies the name of the table or the view on which the index is to be created.
Column_name specifies the name of column or columns on which the index will be created.
Example
- Create Clustered Index EmpIndex
- On Employee(EmpID)
-
- Create Nonclustered Index MgrIndex
- On Employee(MgrID)