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.