Indexes In SQL Server

About Index

When it comes to performance tuning, the queries should be re-written in the best way to fetch the data For this we create indexes to boost the performance. SQL Server use indexes to find data quickly when a query is processed.

An index is a database object that is created and maintained by the DBMS. Indexed columns are ordered or sorted so that data searching is extremely fast. An index can be applied on a Column or a View. A table can have more than one index.

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put an index as a pointer to data in a table.

Indexes can be created or dropped without having any effect on the data values of a table.

Index Layout Structure: B-tree Index format layout as shown below:

Benefits of using Indexes:

• Index is useful for fast retrieval of information.
• Index uses one or more column index keys and pointers to the record to find that record.
• To speed up query performance.

Example: If an index is created on Primary key of a table and then search for a row based on Primary key value then SQL Server first finds that value in the index and then uses the index to locate the row in the table instead of performing a complete table scan.

Drawback of Indexes:

• Indexes should not be used on small tables.
• Indexes are stored on the disk and the amount of space required will depend on the size of the table.
• Do not use Index on Tables that have frequent, large batch update or insert operations.

To see Index used space and Database free space Execute command on your selected database:

Syntax:  Sp_spaceused

Example: Database Name: Trainee with table name Trainee1, Index size details:

 

 Types of Index: We have options of using Indexes that are of of the following types:

1. Clustered: Only 1 clustered index per table we can create as same as the table records are ordered. It is very efficient to search for fast access of query.
   
   
2. Non-Clustered: In SQL Server 2014 only 999 Non-clustered Index per table creation is allowed. Table order will remain same but the Index order will be in Non-clustered layout. So to search a last column value retrieval will be faster in this case.
   
   
3. Composite: Composite indexes can be of clustered and non-clustered indexes. Index containing more than one column or maximum 16 Columns is allowed.
   
   
4. Unique:  We create Unique Index to provide Uniqueness of Index Column values. When a Primary Key or Unique Constraint is used on a table row, a Unique Index will get created automatically to avoid Duplicity.

Creating Index: You can create Index by query or through Wizard. I have a database with name Trainee, you can see in table name dbo.CustomerOrders Indexes section is empty as seen below:



Single Column Index: Through Query:

Syntax: CREATE INDEX index_name ON table_name (column_name);

Example: Index AmountDetails is created below:




Through Wizard: Select your database(ex: Trainee), then select your Table (Ex: dbo.CustomerOrders). After that right click on Indexes option in it, then New Indexes. Now select option Clustered or Non-Clustered Index as you can see in the following:




I selected Non Clustered Indexes: Fill Index name and Add column by clicking on Add option. After that click Ok.



Multiple Column Index:

Syntax: CREATE INDEX index_name on table_name (column1, column2);

Example: Ordersondate index created with OrderID, OrderDate column

 

Unique Index:

Syntax: CREATE Unique Index IndexName ON table (column ,[, .,..]);

Drop Index:

Syntax: DROP INDEX index_name ON table_name

Example: To Drop an existing Index that is already created with Name: OrderDate

 

The following query is performed: OrderDate Index will be deleted as in the following,