SQL Server Index Overview

A table or view can contain the following types of indexes:

• Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.  
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

• Nonclustered

• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

Create Clustered index:

CREATE TABLE Employees
(
ID CHAR(900),
FirstName NVARCHAR(3000),
LastName NVARCHAR(3000),
StartYear CHAR(900)
)
GO
CREATE CLUSTERED INDEX IX_Clustered

Create Non-Clustered index:

CREATE TABLE Employees
(
ID CHAR(900),
FirstName NVARCHAR(3000),
LastName NVARCHAR(3000),
StartYear CHAR(900)
)
GO
CREATE NONCLUSTERED INDEX IX_NonClustered
ON Employees(StartYear)
GO

Show index info:

SP_HELPINDEX tablename

Drop index:

DROP INDEX IX_NonClustered ON Employees

Returns size and fragmentation indexes:

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }``
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)
Example:
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2017'), OBJECT_ID(N'IX_Address_StateProvinceID'), NULL, NULL , 'DETAILED');

Rebuild or reorganize all indexes on a table

avg_fragmentation_in_percent value Corrective statement

>5% and < = 30% REORGANIZE

>30%                     REBUILD

Rebuilding indexes is done using the following statement

ALTER INDEX All ON tableName REBUILD;

This drops the index and recreates it, removing fragementation, reclaims disk space and reorders index pages. 

One can also reorganize an index using

ALTER INDEX All ON tableName REORGANIZE;

which will use minimal system resources and defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.

Rebuild all index database

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'