An index in SQL is a quick lookup table used to find records that users frequently search for. An index is designed to be small, fast, and optimized for quick lookups. It is extremely useful for linking relational tables and searching large tables.
An index is a collection of keys derived from one or more columns in a table or view. These keys are stored in a structure (B-tree) that allows SQL Server to quickly and efficiently find the row or rows associated with the key values.
The indexes are classified into clustered, non-clustered, unique Index, spatial, column store, filtered and hash indexes. However, widely used indexes are clustered, unique and non-clustered indexes.
A clustered index is an index that specifies the physical arrangement of a database’s table records. There can only be one clustered index per table since there can only be one method that records are physically stored in a database table. On a primary key column, a clustered index is automatically built. Only tables are sorted by clustered indexes. They don’t require more storage as a result.
Non-clustered indexes take up more storage because they are kept apart from the real table. Since there is no additional lookup step required, clustered indexes are quicker than non-clustered ones.
Because the data is logically sorted according to the key, a clustered index is advantageous for range queries. By building a new clustered index on a separate filegroup, you can relocate a table to a different filegroup. As you would when moving a heap, you do not need to drop the table. All nonclustered indexes contain a clustering key.
The table’s primary keys are clustered index by default. It can be used in conjunction with the table’s unique constraint, which serves as a composite key. The performance of data retrieval can be enhanced using a clustered index. It ought to be made on the columns that are used for joins.
A spatial index allows you to perform certain operations on spatial objects (spatial data) in a column of the geometry data type more efficiently. The spatial index reduces the number of objects that must be subjected to relatively expensive spatial operations.
The unique index enforces the uniqueness of values in one or more columns.
Let’s see some examples:
CREATE INDEX idx_RegNumber ON tbStudents (RegNumber);
CREATE INDEX idx_RegNameBatchID ON tbStudents (RegNumber, BatchNumber);
In the first case, we’re creating a non-clustered index on the registration number, and in the second case, we’re creating a non-clustered index on the registration number and batch number columns. Creating an index on multiple columns called the Composite index.
Also, in the example above, we didn’t specify the index type explicitly since it is optional. It can be written as shown below.
CREATE NONCLUSTERED INDEX idx_RegNumber ON tbStudents (RegNumber);
Unique indexes should be written as shown below.
CREATE UNIQUE INDEX idx_unq_regnumber ON tbStudents(RegNumber)
CREATE UNIQUE INDEX idx_unq_regnumber ON tbStudents(RegNumber, BatchNumber)
Clustered indexes should be written as shown below.
CREATE CLUSTERED INDEX idx_batch_tbStudent ON dbo.dbStudent(BatchNumber);
Columnstore indexes should be written as shown below.
CREATE CLUSTERED COLUMNSTORE INDEX idx_tbStudent_GroupType
ON tbStudent.GroupType
WITH (DATA_COMPRESSION = COLUMNSTORE);
The Include keyword in indexes should be written as shown below.
CREATE NONCLUSTERED INDEX idx_tbStudent_JoinDate_incUpdatedDate
ON tbStudent (JoinDate)
INCLUDE (dtUpdatedDate);
Hope you find this article helpful.
Big Data and SQL 
One comment