Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow.
The same applies to indexes in databases. Without indexes, a DBMS has to go through all the records in the table in order to retrieve the desired results. This process is called table-scanning and is extremely slow. On the other hand, if you create indexes, the database goes to that index first and then retrieves the corresponding table records directly.
In this post, we shall learn below types of indexes that are available in SQL server
1. Clustered Index
2. Non Clustered Index
3. Unique Index
4. Full Text Index
1. Clustered Index
2. Non Clustered Index
3. Unique Index
4. Full Text Index
A Clustered index defines the order in which data is physically stored in a table. It is similar to how words are stored and sorted in English dictionary based on alphabetically ascending order. Similarly, in SQL server, the primary key constraint automatically creates a clustered index on that particular column. Thus the table data records are stored based on primary key which forms the clustered index. Therefore, there can be only one clustered index per table.
Example: Let us take an example.
CREATE TABLE student
(
student_ID INT PRIMARY KEY,
student_Name VARCHAR(50) NOT NULL,
student_gender VARCHAR(50) NOT NULL,
student_DOB datetime NOT NULL,
student_total_score INT NOT NULL,
)
Notice here in the “student” table we have set primary key constraint on the “student_ID” column. This automatically creates a clustered index on the “student_ID” column. This clustered index stores the record in the student table in the ascending order of the “student_ID”.
Example: Let us take an example.
CREATE TABLE student
(
student_ID INT PRIMARY KEY,
student_Name VARCHAR(50) NOT NULL,
student_gender VARCHAR(50) NOT NULL,
student_DOB datetime NOT NULL,
student_total_score INT NOT NULL,
)
Notice here in the “student” table we have set primary key constraint on the “student_ID” column. This automatically creates a clustered index on the “student_ID” column. This clustered index stores the record in the student table in the ascending order of the “student_ID”.
A Non Clustered Index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another at the end of the text book index section. SQL Server allows you to create almost 999 non clustered indexes per table. The non clustered index in Sql Server is very useful to improve the query performance.
Example: Let us take an example.
The above script creates a non-clustered index on the “student_Name” column of the student table. The index sorts by "student name" in ascending order. As discussed earlier, the table data and index will be stored in different places. The table records will be sorted by a clustered index. The index will be sorted according to its definition and will be stored separately from the table.
Example: Let us take an example.
CREATE NONCLUSTERED INDEX IX_tblStudent_Name
ON student(student_Name ASC)
The above script creates a non-clustered index on the “student_Name” column of the student table. The index sorts by "student name" in ascending order. As discussed earlier, the table data and index will be stored in different places. The table records will be sorted by a clustered index. The index will be sorted according to its definition and will be stored separately from the table.
Difference between clustered index and non clustered index:
1. It is important to mention that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index, data is stored in the specified order.
2. There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
3. Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
4. Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.
1. It is important to mention that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index, data is stored in the specified order.
2. There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
3. Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
4. Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.
Unique index: To enforce the uniqueness value of one or more columns, you often use the PRIMARY KEY constraint. However, each table can have only one primary key. Hence, if you want to have a more than one column or a set of columns with unique values, you cannot use the primary key constraint.Luckily, SQL server provides another kind of index called UNIQUE index that allows you to enforce the uniqueness of values in one or more columns. Unlike the PRIMARY KEY index, you can have more than one UNIQUE index per table.
To create a UNIQUE index, you use the CREATE UNIQUE INDEX statement as follows:
Example:
Example:
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
ON table_name(index_column_1,index_column_2,...);
Full Text Index: Full-text search refers to the functionality in SQL Server that supports full-text queries against character-based data. These types of queries can include words and phrases as well as multiple forms of a word or phrase. To support full-text queries, full-text indexes must be implemented on the columns referenced in the query. The columns can be configured with character data types (such as char and varchar) or with binary data types (such as varbinary and image). A full-text index is made up of word tokens that are derived from the text being indexed. For example, if the indexed text contains the phrase “tables can include indexes,” the full-text index would contain four tokens: “tables,” “can,” “include,” and “indexes.” Because the list of tokens can be easily searched, full-text queries can quickly locate the necessary records.