Sql Server

SQL | Indexes

SQL | Indexes

SQL | Indexes

Indexes are special data structures associated with tables or views that help speed up the query.
It makes the querying process fast by providing easy access to rows in data tables

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements.
These indexes need extra space on the disk, but they allow faster search according to different frequently searched values.

ON table_name column_name;  

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX idx_lastname
ON Persons (LastName);


Creates a unique index on a table. Duplicate values are not allowed:

ON table_name (column1, column2, ...);

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);


-- Drop A Single Index
DROP INDEX [IF EXISTS] index_name ON table_name;

-- Drop Index on Mulitple Tables
index_name1 ON table_name1,  
index_name2 ON table_name2,  

Rname an index?
Using system stored procedure sp_rename

EXEC sp_rename  

We can also rename it directly using UI of SQL Server Management Studio   

Disable Indexes
We can disable the indexes in SQL Server by using the ALTER INDEX statement

-- Disable single Index
ALTER INDEX index_name  
ON table_name  

--Disable all indexes of a table in the current database

Enable Indexes
If we want to enable the disabled index in the SQL Server, we need to rebuild it because we cannot simply enable it.

-- Using the ALTER INDEX statement
ALTER INDEX index_name  
ON table_name    

-- Using DBCC DBREINDEX command
DBCC DBREINDEX (table_name, index_name);  

Filtering Indexes
The filtered index concept comes into play when our applications just need to query a portion of a table's rows. A filtered index is a non-clustered index with a predicate that lets us select which rows to include in the index.

CREATE INDEX index_name  
ON table_name(column_list)  
WHERE predicate

Benefits of filtered indexes:
Filtered indexes can save spaces, especially when the index key columns are sparse. Columns with a lot of NULL values are considered sparse.
It also reduces maintenance costs because only a part of the data rows will be changed when the data in the related table changes, rather than all of them.

Types of SQL Server Indexes
SQL Server provides two types of indexes: clustered index and non-clustered index.

Clustered indexes
Clustered indexes use key values for sorting and storing data rows in tables or view. They are included in the index definition.
It always stores the index value in a B-tree structure where the actual data is stored in the leaf node.
Since the data rows are stored in one direction, each table can only have a single clustered index.
A table stored the rows in sorted order only when the table has a clustered index. We can refer to a clustered table as one that has a clustered index.

Non-Clustered indexes
The structure of non-clustered indexes is similar to the clustered index except that the actual data is not contained in the leaf nodes.
A non-clustered index has the non-clustered index key values, and each key-value entry contains a reference to the actual data.
All of its data rows are stored in a heap, which is an unordered structure.

Depending on how the table data is stored, it could point to a data value in the clustered index or a heap structure.
If a row locator is a pointer to the row, it is a heap structure. If a row locator is the clustered index key, it is a clustered table.

When should indexes be created?
When a column has a wide range of values
When the column does not have a large number of null values
When single or multiple columns used together in a where or join clause

When should indexes be avoided?
When a table is small
When the columns aren't used as a query condition
When the column is constantly updated

Related Post

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.