Performance issues are a big deal in any database system and SQL Server is no exception. There are several things we can look at when faced with these issues and one of these is a misuse or lack of use of indexes. Without carefully designing indexes, the whole system can suffer so it’s a good idea to have a proper SQL Server Index Design plan as part of your Database tables. In this guide we will explore the best techniques to design and implement efficient indexes. There are several different types of indexes a person can create but, in this article, we will focus on the most common of these; clustered indexes, non-clustered indexes, and unique indexes.

A database table can only have one clustered index as a clustered index sorts the entire table according to that index. The actual data is stored in this organized manner. A table without a clustered index is known as a heap. (Clustered and Nonclustered Indexes Described, 2017) Clustered indexes allow for quicker searches and are automatically set on the table’s primary key. However, it makes more sense and can help tremendously if another column is chosen according to the needs and requirements of the business. (What is difference between Clustered Index and Non-Cluster Index?, 2014)

Non-clustered indexes do not sort the data at hand but create a separate structure, which is sorted according to the index, with pointers to the location of the data. This pointer is known as the row locator. Non-clustered indexes would consume more space since a separate structure is created every time.

A unique index forces the database engine to ensure that there are no duplicates added. All rows are rolled back if a duplicate is found. However, with the IGNORE_DUP_KEY clause set on, only the duplicated rows will fail. (CREATE INDEX (Transact-SQL), 2018)

An index can be created using the following script snippet taken from Microsoft Docs site.

(CREATE INDEX
(Transact-SQL), 2018)

References

Clustered and Nonclustered Indexes Described. (2017, November 11). Retrieved from Microsoft Docs:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017

CREATE INDEX (Transact-SQL). (2018, September 9). Retrieved from Microsoft Docs:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017#unique-indexes

What is the difference between Clustered Index and
Non-Cluster Index? (2014, October
27). Retrieved from IT Knowledge Exchange: https://itknowledgeexchange.techtarget.com/itanswers/cluster-index/