<< European ASP.NET Ajax Hosting :: What is AJAX Security? | SQL Server Hosting - HostForLIFE :: Group By, Having, and Where Clauses In SQL >>

SQL Server Hosting - HostForLIFE :: Quick View Of Indexes In SQL Server

June 15, 2021 09:36 by author

Peter

This section is all about Indexes in SQL Server. We will learn about indexes in SQL Server including how to create the index, rename index, drop index, and more.
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/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 the 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, the primary key of the table is used as the key for the clustered index.

A 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 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, what we have learned,
    INDEXES
    Types of Indexes
    How to Rename Indexes
    How to Drop Indexes

If you guys have any questions let me know.