SQL Server Indexes

Go Back

Level : Advanced

Go Back

Mentor: Shailendra Chauhan

Duration : 00:02:00

Get Details

Hash Index

A hash index in SQL Server is used to quickly locate rows based on the hash value of one or more columns. It is particularly useful for equality-based searches.

Example

CREATE NONCLUSTERED HASH INDEX IX_HashIndex
ON dbo.TableName (ColumnName);

Memory-Optimized Nonclustered Index

Memory-optimized nonclustered indexes are used in SQL Server's In-Memory OLTP feature to accelerate read operations on memory-optimized tables.

Example

CREATE NONCLUSTERED INDEX IX_MemoryOptimized
ON dbo.MemoryOptimizedTable (Column1, Column2);

Clustered Index

A clustered index determines the physical order of data rows in a table. Each table can have only one clustered index.

Example

CREATE CLUSTERED INDEX IX_Clustered
ON dbo.TableName (PrimaryKeyColumn);

Nonclustered Index

Nonclustered indexes provide an additional structure to quickly retrieve data rows based on the specified columns without affecting the physical order of the table.

Example

CREATE NONCLUSTERED INDEX IX_NonClustered
ON dbo.TableName (Column1, Column2);

Unique Index

A unique index enforces the uniqueness constraint on one or more columns, ensuring that no duplicate values are allowed.

Example

CREATE UNIQUE INDEX IX_Unique
ON dbo.TableName (UniqueColumn);

Columnstore Index

Columnstore indexes are designed to improve the performance of analytical queries by storing and managing data in columnar format.

Example

CREATE CLUSTERED COLUMNSTORE INDEX IX_Columnstore
ON dbo.TableName (Column1, Column2);

Index with Included Columns

An index with included columns includes additional non-key columns in the index leaf nodes, reducing the need to access the base table for certain queries.

Example

CREATE NONCLUSTERED INDEX IX_IncludedColumns
ON dbo.TableName (KeyColumn)
INCLUDE (Column1, Column2);

Index on Computed Columns

You can create an index on computed columns, which are generated based on expressions involving other columns.

Example

CREATE NONCLUSTERED INDEX IX_Computed
ON dbo.TableName (ComputedColumn);

Filtered Index

A filtered index is created with a filter condition to index only a subset of rows in a table, optimizing query performance for specific queries.

Example

CREATE NONCLUSTERED INDEX IX_Filtered
ON dbo.TableName (Column1)
WHERE Column2 = 'Value';

Spatial Index

Spatial indexes are used to improve the performance of spatial data queries, such as those involving geometry or geography data types.

Example

CREATE SPATIAL INDEX IX_Spatial
ON dbo.SpatialTable (SpatialColumn);

XML Index

XML indexes are used to speed up XML data type queries, making it more efficient to search and retrieve XML data.

Example

CREATE PRIMARY XML INDEX IX_Xml
ON dbo.XmlTable (XmlColumn);

Full-Text Index

Full-text indexes are used for efficient text-based searching within columns containing large amounts of text data.

Example

CREATE FULLTEXT INDEX IX_FullText
ON dbo.TextTable (TextColumn)
KEY INDEX PK_TextTable;

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.