Types of Indexing
In the (previous blog) Importance of Indexing and efficiency of indexing – Part 1 we had gone through the basics of indexing and how to create an Index. Now we are going to know about the Types of indices.
There are different types of indices that can be created against a Table. But, the main goal of all of it to improve database query performance.
- Single Column Index
- Unique Index
- Composite Index
Single Column Index:
Indexing on a single column of a table is the simplest and most widely used index.
Single Column Index is the now which is created based on only one column of a Table. Yeah, you are correct, in the previous blog we have created single column index.
Syntax:
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);
Example:
CREATE INDEX index_employees_on_phone_number ON employees(phone_number);
Unique Index:
Unique index is like Single Column Index, but it does not allow duplicate values to be inserted on the Table based on the Indexed column.
Its not only for performance but also for data integrity.
In the previous example, we have indexed the “phone_number” column on the Employee table, we can use the same column for the Unique Index also.
Syntax:
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);
Example:
CREATE UNIQUE INDEX index_employees_on_phone_number ON employees(phone_number);
Unique indices may apply on person’s Social Security Number, Employee ID and etc. In our case, Phone Number of each Employee should be different.
Composite Index:
Composite Index is an index created on more than one column of a table.
There will be performance issue will occur while creating the Composite Index. For that we have to properly order the column while creating the Index. The ordering of the column in Composite Index play a vital role in data retrieval. (Lets look into that deeply on upcoming blog)
Syntax:
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_1, COLUMN_2);
Example:
CREATE UNIQUE INDEX index_employees_on_last_name_dept ON employees(last_name, dept);
Note:
You have to decide whether to use Single Column Index or Composite Index based upon the columns which are you going to frequently query.
Clustered Index:
Clustered Index stores and sorts the data based on the column (or columns) which are specified for the Index. Clustered index contains all the data for the table in the index, sorted by the index key.
If the database engine can use a clustered index during a query, the database don’t need to follow the reference back to the rest of the data like normal indices. The result is less work for the database and consequently, better performance for a query using a clustered index.
Syntax:
CREATE CLUSTERED INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
Example:
CREATE CLUSTERED INDEX index_employees_on_social_security_number ON employees(social_security_number);
As a general rule of thumb, every table should have a clustered index. If you create only one index for a table, use a clustered index. Not only is a clustered index more efficient than other indexes for retrieval operations, a clustered index also helps the database efficiently manage the space required to store the table. In SQL Server, creating a primary key constraint will automatically create a clustered index (if none exists) using the primary key column as the index key.
The suitable example for Clustered Index is Telephone Directory, at where the data are sorted alphabetically.
Note:
- Only one Clustered Index can exist for a table.
- Its good practice to create a Clustered Index on Primary key and Foreign key column, because key values generally do not change.
Disadvantage of Clustered Index:
If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance.
Non-Clustered Index:
Non-Clustered Index contains index key value and a reference to the actual data. If there is no clustered index, the row locator is a pointer to the row. When there is a clustered index present, the row locator is the clustered index key for the row.
Non-clustered indexes can be optimized to satisfy more queries, improve query response times, and reduce index size. There can be more than one Non-Clustered Index allowed for a Table.
The suitable example for Non-Clustered Index is the Index of a Book, at the words with the reference of its page number.
Syntax:
CREATE NONCLUSTERED INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
Example:
CREATE NONCLUSTERED INDEX index_employees_on_last_name ON employees(last_name);
Difference between Clustered and Non-Clustered Indices:
Lets see you in the next blog about “How ordering the column in a Composite Index plays an important role in increasing the performance”.
Tell us about the before-and-after. I bet you’ll have something to say!!!