This article provides a simple definition of indexes, their types, how to create them and the difference between clustered and non- clustered indexes.
An Index is a database object that can be created on one or more columns (16 max column combinations). When creating the index it will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and add some overhead to the data modification such as create, delete and modify. So it depends on how much data retrieval can be done on table versus how much of DML (Insert, Delete and Update) operations.
An index is basically used for fast data retrieval from the database. For example, if you want to reference all pages in a book that explains a certain topic, you first refer to the index that lists all the topics alphabetically and are then referred to one or more specific page numbers.
Types of Index
In a SQL Server database there are mainly the following two types of indexes,
- Clustered index and
- Non Clustered index
Clustered Index
In a simple way a Primary key is a Clustered index.
Non Clustered Index
A Unique key is a Non-Clustered index.
Syntax to create Index
The basic syntax of CREATE INDEX is as follows:
- CREATE INDEX index_name ON table_name;
The DROP INDEX Command
An index can be dropped using the SQL DROP command. Care should be taken when dropping an index because performance may be degraded or improved.
The basic syntax is as follows:
Difference between Clustered Index and Non Clustered Index in SQL Server
- One of the main differences between clustered and non-clustered indexes in SQL Server is that, one table can only have one clustered Index but It can have many non-clustered indexes, approximately 250 because basically a Primary key is a Clustered index and a Unique key is a Non-Clustered index and one table can have only one primary.
- Basically a Primary key is a Clustered index and a Unique key is a Non-Clustered index.
- A clustered index determines the order in which the rows of the table will be stored on disk. A non-clustered index has no effect on which the order of the rows will be stored.
- Non-clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don't need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index.
- Non-Clustered Indexes require more space compared to Clustered indexes because in Non-Clustered indexes one separate table is maintained.
Here I mostly focused on what indexes are and the types of indexes in SQL Server. I hope you like my articles.