SQL Index

SQL Index is used to improve query performance. The indexed table retrieves data faster. An index is a schema object or on-disk structure which speeds data retrieval from tables or views. An index is built on single or multiple columns in a table.

For example, In a book, we have an index page where the list of topics is given with their page number. When we want to read a specific topic then we refer index page and lookup for the associated topic page number. Once we get the page number we can quickly access that topic directly jumping on the page number.
If we don’t have an index page in the book then it will take time to search our required topic.

There are SQL index commands to create, modify and delete indexes, CREATE INDEX command is used to create new indexes, the DROP INDEX command is used to delete indexes, and ALTER INDEX command is used to modify existing indexes. Indexes are stored in a B-tree structure which improves data traversal in a table.

SQL Index

 

Types of SQL Index

 

Clustered Index

 

The Clustered Index stores and sorts table data based on key values or key columns. The columns are included in the index definition. We can define only one index per table because the data can be sorted physically only one time for a particular table. It uses the B-tree structure to store the data, where the leaf node contains the actual data. If we don’t define the clustered index on a table then data will be stored in an unordered structure that is Heap structure. The clustered index provides a faster data retrieval facility.

syntax
CREATE  CLUSTERED  INDEX indexname  
ON tablename columnname;   

example 
CREATE  CLUSTERED  INDEX idx_studid 
ON dbo. students studid;

In the above example, Clustered Index is created on column studid of dbo.students (dbo is schema name and students is a table name ) table with the sql index name idx_studid
always use the column name of a particular table while defining index name (in above example idx_studid is index name where studid is column name used in index name)

Non-Clustered Index

In a Non-Clustered index, the index structure and data are kept separately. It stores sql index key values where each key value points to their respective data rows. The pointer from the index row to the data row is known as the row locator. The row locator structure depends upon how the data pages are sorted. The actual data is not contained in the leaf node, instead, it contains a pointer to the data row. We can define multiple non-clustered indexes on a table. We can not create a non-clustered index on temporary tables.

syntax
CREATE  NONCLUSTERED  INDEX indexname  
ON tablename columnname;   

example 
CREATE  NONCLUSTERED  INDEX idx_deptid 
ON dbo. students deptid;

In the above example Non-Clustered index is created on deptid column of dbo.students with “idx_deptid” index name.

Unique Index

The unique index ensures that the sql index column contains no duplicate records that is data must be unique in the key column.

syntax
CREATE  UNIQUE  INDEX indexname  
ON tablename columnname;

CREATE UNIQUE INDEX uidx_userdtsID
ON dbo.usersdts (userdtsID);

In the above example unique index was created on dbo.usersdts table on userdtsID column with uidx_userdtsID index name. uidx_userdtsID column stores unique data in the column.

When we can create the SQL index

  • A table contains huge data
  • The key column should have minimal null values
  • The columns which are frequently used in select, join operations

When we can avoid the SQL index

  • There is less data in a table
  • Frequent data modifications on key columns.
  • Columns are referred rarely in database operations

 

Rename index

Sometimes the user needs to change the existing or default index name which is not meaningful and easily understandable. Then in that case user can rename the sql index with the appropriate index name. The SQL index can be renamed using system stored procedure sp_rename.

EXEC sp_rename   
    indexname,   
    new_indexname,   
    N'INDEX';    

example 

exec  sp_rename 'dbo.students.ix_depnmid','ix_stud_depid',N'INDEX';

In the above example, the index ix_depnmid is renamed to ix_stud_depid on a table dbo.students

Modify Index

Disable Index

If we want to disable the existing sql index we have to use ALTER INDEX and disable keyword.

syntax

ALTER INDEX indexname  
ON tablename  
DISABLE;  

example 

ALTER INDEX ix_stud_depid
ON dbo.students  
DISABLE;

In the above example, ix_stud_depid index will be disabled in dbo.students table.

Enable Index

Using alter index we can enable the disabled sql index.

syntax

ALTER INDEX indexname  
ON tablename  
REBUILD;  

example 

ALTER INDEX  ix_stud_depid  
ON dbo.students  
REBUILD;

In the above example ix_stud_depid index will be enabled on dbo.students table.

Remove/DROP INDEX

We can remove or drop the sql index using the drop index command.

syntax

DROP INDEX indexname  
ON tablename 
 

Example 

DROP INDEX  ix_stud_depid  
ON dbo.students

The ix_stud_depid index will dropped from dbo.students table.

 

Advantages of Indexes

Accelerated Searches: Consider indexes as turbochargers for data retrieval. They save time by indicating precisely where to search, especially when dealing with substantial datasets.

Effortless Data Arrangement: Indexes simplify the task of organizing data neatly. This becomes valuable when you require data in specific orders, such as alphabetically sorting names or arranging dates from oldest to newest.

Duplicate Data Prevention: Indexes guarantee the absence of redundant entries within a database, ensuring data accuracy and dependability.

Facilitating Data Associations: In the realm of databases, indexes serve as connectors that proficiently link related pieces of information. This preserves data relationships and brings coherence to the dataset.

Reduced Computational Burden: Indexes provide precise directions to the computer, eliminating the need for exhaustive searches. This not only conserves time and effort but also ensures smooth system operation.

Drawbacks of Indexes

Increased Storage Requirement: Indexes demand additional storage space within your database. If you create numerous indexes, it can lead to a heightened need for storage, potentially posing challenges in storage-constrained environments.

Slower Data Updates: When data is added, modified, or deleted, indexes must also undergo updates. This additional process can marginally decelerate these operations.

Necessity for Routine Maintenance: Indexes call for periodic upkeep to maintain their efficiency. As data evolves, indexes can become disorganized, potentially hindering performance.

Limited Applicability: Indexes shine brightest in swiftly locating specific data. However, their utility may diminish when dealing with tasks such as data summarization or handling vast datasets.

Prudent Selection Required: The process of deciding which data to index and how to configure indexes can be intricate. Incorrect choices or improper index setup may yield undesired outcomes.

Concurrency Challenges During Peak Times: In highly active systems, a profusion of indexes can occasionally lead to delays for multiple users attempting to access data simultaneously. Prudent planning is essential to avoid such issues.