We will discuss the various categories of indexes in the Microsoft SQL Server. The main types of indexes are: clustered indexes, non-clustered indexes, BTREE indexes, and unique indexes.
Types of Indexes in Microsoft SQL Server
Clustered Index
A clustered index defines the actual physical arrangement of the data within a table. Every table in the SQL Server can have only one clustered index, and the index must be created on a unique, non-null column or set of columns. Since a clustered index determines the physical layout of a table, it is often used for tables that are frequently searched based on their primary key or other unique values.
Let us first create a table and insert the values into it using the following SQL commands:
VALUES (1, 'Somdeb Nath', 'somdebnath@example.com', '3532626'),
(2, 'Jina Pal', 'paljina@example.com', '5555678'),
(3, 'Arnita Guha', 'rjarnita@example.com', '4449912'),
(4, 'Krishna Kumar', 'kk@example.com', '716781497');
Let’s look at an example of a clustered index. To create a clustered index on the “customer_id” column, we can use the following SQL query:
This creates a clustered index on the customer_name column where the data in the table is physically ordered based on the values in the customer_name column.
To fetch all the customers whose name starts with letter “A”, we can use the following SQL query:
FROM customers
WHERE customer_name LIKE 'A%'
ORDER BY customer_name;
Output:
1 3 Arnita Guha rjarnita@example.com 4449912
Non-Clustered Index
It is a type of index that does not affect the physical order of the data in a table. A non-clustered index produces an independent data structure that stores the index key and pointer to the associated data row in the table. This allows the queries to quickly locate the relevant data rows based on the values in the index. Unlike the clustered indexes, the tables in the SQL Server can have multiple non-clustered indexes, and the index can be created on any column or set of columns in the table.
An example of a non-clustered index is as follows:
ON customers (customer_email);
This creates a non-clustered index on the “customer_email” column where the data in the table remains physically unordered, but the index stores a sorted copy of the data in the “customer_email” column.
To fetch all customers whose email address includes the “gmail.com” domain, we can use the following SQL query:
FROM customers
WHERE customer_email LIKE '%gmail.com%'
ORDER BY customer_name;
Output:
Here, no customer has an email that contains the “gmail.com” domain, so the output field is empty.
BTREE Index
A BTREE index is a way of organizing the data in a structure that resembles a tree. Each node in the tree contains a range of key values, and each leaf node contains a pointer to the corresponding data row. BTREE indexes are commonly used in the SQL Server because they allow for efficient searching and sorting of large amounts of data. They are especially useful for queries that involve range searches or sorting operations. For example: salaries, phone numbers, etc.
An example of creating a BTREE index on the “customer_phone” column is as follows:
ON customers (customer_phone);
This creates a B-tree index on the “customer_phone” column where the data in the index is stored in a tree-like structure, with each node containing a range of values and pointers to the other nodes.
Now, we want to retrieve all customers whose phone number starts with the area code of “555” using the following SQL query:
FROM customers
WHERE customer_phone LIKE '555%'
ORDER BY customer_name;
Output:
1 2 Jina Pal paljina@example.com 5555678
Unique Index
It is a type of index that ensures that no two rows in a table have the same key value. This can be useful to enforce the data integrity and prevent the duplicate records in a table.
An example of creating a unique index on the “customer_email” column is as follows:
ON customers (customer_email);
This creates a unique index on the “customer_email” column where the index enforces a constraint that ensures that no two rows in the table can have the same value in the “customer_email” column.
Now, insert a new customer into the table with the “lili@gmail.com” email and retrieve it using the following SQL query:
VALUES (5, 'Lili Doe', 'lili@gmail.com', '333-333-3333');
select *
FROM customers WHERE customer_email LIKE 'L%';
Output:
1 5 Lili Doe lili@gmail.com 333-333-3333
Conclusion
Indexes play an important role in optimizing the performance of the SQL Server databases. Understanding the different types of indexes can help the database administrators choose the most appropriate type of index for their applications. By effectively creating and maintaining the indexes, businesses can ensure that their databases are performing efficiently, allowing them to make data-driven decisions and provide a better service to their customers.
