INDEXES in SQL Server

This section is all about Indexes in SQL Server. In this article, we will learn about indexes in SQL Server like creating an index, renaming an index, dropping an index, and more.

INDEXES are Special Data Structures associated with tables or views that will help to speed up the data fetching/ queries.

Indexes are similar to the index of a book/ Notebook, Whenever we want to search any topic, we refer to the index to find that page number to access quickly without going through all pages of the book. Indexes in SQL Server work in the same manner, an index can be created with both tables and views.

SQL Server provides two types of indexes:

• Clustered index
• Non-clustered index

Let's explore more closely,

Clustered index:

A clustered index stores data rows in a sorted structure based on its key values. This key is a column or a group of columns on which the sorting will be done. By default, the primary key of the table is used as the key for the clustered index. 

Clustered index is a default phenomenon and there can be only one clustered index in a table because rows can be only sorted in one order

How to create clustered index?

Syntax to create clustered index :

  CREATE CLUSTERED INDEX index_Name

  on Schema_Name.table_name (Columns)

Non-clustered index:

A Non-clustered index is a data structure that improves the speed of data retrieval from tables.

Non-clustered index sorts and stores data separately from the data rows in the table.

How to create non clustered index?

Syntax to create Non-clustered index :

  CREATE [NONCLUSTERED] INDEX index_Name

  on Schema_Name.table_name (Columns)

Some Notable Syntax:

How to check indexes for the mentioned table?

    

        EXEC SP_HELPINDEX 'schema_name.table_name';

How to rename the indexes?

    EXEC SP_RENAME 'OldIndexName', 'NewIndexName', 'INDEX';

How to drop indexes?

DROP INDEX removes one or more indexes from the current database.

    DROP INDEX [IF EXISTS] INDEX_NAME ON TABLE_NAME

To summarize, we have learned:

• INDEXES
• Types of Indexes
• Renaming Indexes
• Drop Indexes