Indexes

An index serves as a navigational marker, analogous to an index in a book, facilitating the swift and efficient retrieval of data from a table. While invisible to users, indexes play a crucial role in accelerating database queries. Their implementation is particularly advisable as the database grows over time. Despite their widespread use, it's essential to note that indexes consume additional memory space, and improper utilization can potentially hamper performance.

Indexes are very useful, and we can see one of their types when we created a primary key that automatically creates an Index clustered. The different types of indexes are:

• Clustered index

• Non-Clustered index

• Unique index

• Composite index

• B-Tree index (Oracle considered Normal indexes as B-Tree Indexes)

• Function based index

• Bit map index

In this post we will talk about clustered and non-clustered Indexes, their use, their characteristics, how the query execution plan read the indexes, why is recommended to have indexes clustered and/or non-clustered, etc.

Clustered Index

A clustered index organizes data rows and index rows within a database. When a clustered index is applied, it arranges the rows in the table or view based on their key values. This sorting facilitates fast information retrieval, as a SELECT operation seeking a specific value can efficiently navigate to the exact data page where that value is stored. The database can optimize performance by caching numerous indexes in memory, enabling the file system to read a substantial number of records simultaneously, rather than retrieving them from the disk individually.

In this context, a "page" refers to a unit of data storage where information is housed. Typically, each table consists of sets of eight pages, and when an index is present, the data within these pages is organized in a structured manner. Conversely, in the absence of an index, a table becomes a heap of data. This scenario is common in data warehousing schemas where the organizational structure is less critical than the efficient storage of vast amounts of data.

In situations where there is a high demand for join operations, SQL Server can rapidly and effortlessly reference indexes instead of performing sequential searches through large tables. This indexing strategy significantly enhances the efficiency of join operations, contributing to overall database performance.

ALTER TABLE [dbo].[Example] ADD  CONSTRAINT [PK_Example] PRIMARY KEY                                                         CLUSTERED 
(
        [Id] ASC
)

Clustered Index main characteristics

• Clustered index sort and store the data and index rows

• Only one single clustered index can be specified in a table

• One or more columns can be used for an index

• Fast return of the data

• Primary Key are by default created as Index clustered

• Index clustered has all the information about where a data is storage in a table

Non-Clustered Index

A non-clustered index is designed to enhance query performance within a table. Multiple non-clustered indexes can exist in a table. Unlike clustered indexes, non-clustered indexes do not physically sort the data; instead, they establish a distinct key-value structure derived from the table. In this structure, the key comprises the column value, and each value contains a pointer to the corresponding data row holding the actual value. Notably, the storage of a non-clustered index is decentralized, residing in separate locations.

CREATE NONCLUSTERED INDEX [IDXNOC] ON [dbo].[Example]
(
        [Description] ASC,
        [Date] ASC,
        [User_Example] ASC
)

Non-Clustered Index main characteristics:

• Store key values only

• Every index row in the non-clustered index stores the non-clustered key value and a row locator

• Bridge to the data

• One or up to 999 non-clustered indexes in a table

• Pointers to Heap/Clustered Index rows

• Operations of Index Scan and Index Seek

• Non-clustered index can create in views

• Add value and other columns

Index non-clustered with the Name column and columns Age and Genre added.

Table scan: Systematically assess each value in the table until the specified data in the query is located. A table scan is employed when querying a heap table.

Index scan: Examine each value in the table until you locate the data specified in the query. An index scan proves to be a suitable solution when querying a table with a relatively small dataset.

Index seek: Instead of scanning the entire index, it efficiently navigates the B-tree structure to locate the value swiftly. Utilizing index seeks is the optimal solution, particularly when working with large datasets. Ensure you filter by the clustered index column for optimal performance.

Indexes are highly recommended if those has a specific and evaluated purpose, indexes are use to retrieve data in a fast way from a large table. If the table wont increase or this is use for data warehousing, indexes could not be necessary. Before creating an index we have to analyse why I need it, what kind of problem I will mitigate, even in the management during a Tuning advisor analyse recommends you, creating an index, is very important to check and investigate that this will be use in the queries.