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 create index, rename index, drop index and more.

Indexes-in-sql-server-dotnetkida

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

Indexes are similar to the index of a book/ Note book, Whenever we want to search any topic, we refers to the index to find  that page number to access quickly without going through all pages of the book. Indexes in SQL Server works 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, 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:

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 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 current database.

    DROP INDEX [IF EXISTS] INDEX_NAME ON TABLE_NAME

To summarize, we have learned:
  • INDEXES
  • Types of Indexes
  • Renaming Indexes
  • Drop Indexes

Post a Comment

0 Comments