In this part we will cover the SQL Indexes and their related interview questions mentioned below one by one.
1) What are SQL Indexes?
2) How to create Indexes in SQL?
3) What are the types of Indexes in SQL?
4) When we have to use Indexing in SQL?
1. What are SQL Indexes?
Index used for fast searching of data.
It provides a fast way to lookup data based on the values with in column in a tables so that it could help to speed up performance of queries.
Indexes in SQL works like Index of books.
It always helps to find data or fetch records in fast way.
If you want to find a particular topic in book, you will first refer to the Index of book,
In Index you can find easily topic by using page number because all pages with topics listed in index page.
Indexes helps to speed up SELECT queries but when we use UPDATE and INSERT statement,
Index takes more space on disk and slow down the performance of Queries.
2. How to create Indexes in SQL?
Creation of Index:
Syntax of Index is mentioned below:
CREATE INDEX Index_Name ON Table_name (Column)
By using below syntax, we can confirm index which created on specific table.
EXEC SP_HELPINDEX Table_name
If you want to remove index from table, you can use the syntax:
DROP INDEX Index_Name on Table_Name
3. What are the types of Indexes in SQL?
Types of Index:
Clustered Index :
• If you created Clustered Index on a table, it will help to sort the data in ascending order or descending order.
• By default, It create on table when define a primary key.
• It reorders the physical order of a table.
• There can be only one clustered Index on Table.
NON-Clustered Index :
• It cannot be sorted like clustered index.
• It maintains logical order of data.
• You can create more than one NON-Clustered Index on column in a table.
• You can create multiple NON_CLUSTERED Index on a single column.
4. When we have to use Indexing in SQL?
When to use Index:
• Firstly, you will check the query and WHERE Clause. If Query is executing again and again, you can create Index on specific column to speed up the performance of query.
• If there are no more NULL values in column on which you want to create index.
• If you want to sort the data, you can create Clustered Index on Column in a table.