How to create, query, and delete MSSQL Index

Let’s learn how to create, query, and delete MSSQL Indexes and practice with source code.

How to create an Index

Creating a Clustered Index.

• Only one Clustered Index can be created per table.
• When a Clustered Index is created, the data rows of the table are physically rearranged based on the corresponding Index key.
• When a Primary Key is created, a Clustered Index is automatically created.

Source Code.

CREATE TABLE AurumGuide_Index (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
-- Create Sample Data
INSERT INTO dbo.AurumGuide_Index(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda');
 
/* CLUSTERED Index CREATE */
CREATE CLUSTERED INDEX AurumGuide_Index_01 ON dbo.AurumGuide_Index (AurumId);

Create a non-clustered index.

• NON CLUSTERED INDEX stores the address (RID) of the data row in a separate index page.
• Non-Clustered Index creates an index that sorts the specified column while leaving the table data as is. It does not touch the data page.
• Non-Clustered Index is slow to search, but data input, update, and deletion are fast.
• Non-Clustered Index can create as many indexes as necessary.
• The NON CLUSTERED keyword can be omitted when creating an index.

Source code.

/* NONCLUSTERED Index CREATE */
CREATE NONCLUSTERED INDEX AurumGuide_Index_02 ON dbo.AurumGuide_Index (AurumId,AurumNm);

Index Lookup Method

How to search by Index name.

• How to search index using sys.indexes.

Source code.

SELECT *
FROM  sys.indexes
WHERE name like 'AurumGuide%'; -- index name

How to search for an index by table name.

• table_name: The name of the table to which the index belongs.
• index_name: The index name.
• column_name: The column used in the index.
• index_type(type_desc): The index type.

Source code.

SELECT a.name AS table_name,
       b.name AS index_name,
       d.name AS column_name,
       b.type_desc AS index_type
FROM sys.tables a
JOIN sys.indexes b ON a.object_id = b.object_id
JOIN sys.index_columns c ON b.object_id = c.object_id 
AND b.index_id = c.index_id
JOIN sys.columns d ON c.object_id = d.object_id 
AND c.column_id = d.column_id
WHERE a.name = 'AurumGuide_index'; -- table name

How to delete or change an index

How to delete an index.

• When deleting an index in use, it is recommended to use the ONLINE option.
• If there is a large amount of data, deleting it will cause a lock, so the online option is required.

Source code.

 /* DROP Index  */
DROP INDEX [AurumGuide_Index_01] ON [dbo].[AurumGuide_Index] WITH ( ONLINE = OFF );

How to change an index.

• Basically, it is impossible to change the INDEX name using T-SQL.
• Of course, there is a way to delete and recreate it, but when changing an index in use, SSMS is recommended.

How to rename using SSMS.