Index Types in Microsoft SQL Server

March 22, 2023 | by Arround The Web | No comments

Index Types in Microsoft SQL Server

An index helps us to search for a particular record in the database. One of the key components of the SQL Server’s data management capabilities is its support for indexing which allows the developers and administrators to optimize the performance of their queries and improve the overall efficiency of their databases.

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:

INSERT INTO customers (customer_id, customer_name, customer_email, customer_phone)

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:

CREATE CLUSTERED INDEX idx_customers_customer_name ON customers (customer_name);

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:

SELECT *

FROM customers

WHERE customer_name LIKE 'A%'

ORDER BY customer_name;

Output:

customer_id customer_name customer_email customer_phone

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:

CREATE NONCLUSTERED INDEX idx_customer_email

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:

SELECT *

FROM customers

WHERE customer_email LIKE '%gmail.com%'

ORDER BY customer_name;

Output:

customer_id customer_name customer_email customer_phone

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:

CREATE INDEX idx_customer_phone

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:

SELECT *

FROM customers

WHERE customer_phone LIKE '555%'

ORDER BY customer_name;

Output:

customer_id customer_name customer_email customer_phone

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:

CREATE INDEX idx_customer_email_new

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:

INSERT INTO customers (customer_id, customer_name, customer_email, customer_phone)

VALUES (5, 'Lili Doe', 'lili@gmail.com', '333-333-3333');

select *

FROM customers WHERE customer_email LIKE 'L%';

Output:

customer_id customer_name customer_email customer_phone

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.

Source: linuxhint.com