Day-21) All about Indexes in sql server with interview questions in hindi |SQL Course
Note: Due to the word limitation you can find all about Non Clustered Index in part-20 of this series.
Indexes in SQL Server
Q.) How will the database engine retrieve the data from a table in SQL Server?
A.) Whenever the database engine wants to retrieve the data from a database table it will adopt two different mechanisms for searching the data
1. Table scan
2. Index Scan/Seek
Q.) What is Table Scan in SQL Server?
A.) In Table Scan, the SQL Server Search Engine will search for the required information sequentially one by one from the start to the last record of the table. If the table has more rows, then it will take more time for searching the required data, so it is a time-consuming process.
Q.) What is Index Scan/Seek in SQL Server?
A.) In Index Scan, the SQL Server Search Engine uses a B-Tree structure to search the required data which drastically improves the performance of your search query by reducing the number of scans. So, let us first understand what B-Tree structure is and how it reduces the number scan which ultimately improves the performance of your search query.
CREATE TABLE Employee
(
Id INT,
Name VARCHAR(50),
Salary INT,
Gender VARCHAR(10),
City VARCHAR(50),
Dept VARCHAR(50)
)
INSERT INTO Employee VALUES (3,'Pranaya', 4500, 'Male', 'New York', 'IT')
INSERT INTO Employee VALUES (1,'Anurag', 2500, 'Male', 'London', 'IT')
INSERT INTO Employee VALUES (4,'Priyanka', 5500, 'Female', 'Tokiyo', 'HR')
INSERT INTO Employee VALUES (5,'Sambit', 3000, 'Male', 'Toronto', 'IT')
INSERT INTO Employee VALUES (7,'Preety', 6500, 'Female', 'Mumbai', 'HR')
INSERT INTO Employee VALUES (6,'Tarun', 4000, 'Male', 'Delhi', 'IT')
INSERT INTO Employee VALUES (2,'Hina', 500, 'Female', 'Sydney', 'HR')
INSERT INTO Employee VALUES (8,'John', 6500, 'Male', 'Mumbai', 'HR')
INSERT INTO Employee VALUES (10,'Pam', 4000, 'Female', 'Delhi', 'IT')
INSERT INTO Employee VALUES (9,'Sara', 500, 'Female', 'London', 'IT')
Creating Index on Id Column:
CREATE CLUSTERED INDEX IX_Employee_ID ON Employee(Id ASC);
What is an Index in SQL Server?
It is a database object in SQL Server which is used to improve the performance of search operations.
When we create an index on any column of a table, then SQL Server internally maintains a separate table called the index table. And when we are trying to retrieve the data from the existing table, depending on the index table, SQL Server directly goes to the table and retrieves the data very quickly.
In a table, we can use a maximum of 1000 indexes (1 Clustered Index plus 999 Non-Clustered Index).
When SQL Server uses Indexes?
The SQL Server uses indexes of a table provided that the select or update or delete statement contained the “WHERE” clause and moreover the where condition column must be an indexed column. If the select statement contains an “ORDER BY” clause then also the indexes can be used.
The syntax for creating an Index in SQL Server:
CREATE [UNIQUE] [CLUSTERED/ NON-CLUSTERED] INDEX (INDEX NAME) ON TABLE NAME (COLUMN LIST)
To see the index: sp_helpindex Employee
To drop an index: Drop index Employee.IX_Employee_Id
Types of indexes in SQL Server
SQL Server Indexes are divided into two types. They are as follows:
1. Clustered index
2. Non-Clustered index
Q.) What is SQL Server Clustered index?
A.) The Clustered Index in SQL Server defines the order in which the data is physically stored in a table. That is the leaf node (Ref to the B-Tree Structure diagram) stores the actual data. As the leaf nodes store the actual data, a table can have only one clustered index. The Clustered Index by default was created when we created the primary key constraint for that table. That means the primary key column creates a clustered index by default.
When a table has a clustered index then the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure.
Understanding the Clustered Index with an example in SQL Server:
In order to understand the clustered index and to make sure that the clustered index by default was created when we created the primary key constraint on a table, please execute the following query.
CREATE TABLE Employee
(
Id INT PRIMARY KEY,
Name VARCHAR(50),
Salary INT,
Gender VARCHAR(10),
City VARCHAR(50),
Dept VARCHAR(50)
)
we created the Employee table having the Id column as the Primary key column. The Primary key constraints in SQL Server by default create clustered indexes if there is no clustered index that already exists on the table. To Confirm whether the Clustered index is created or not execute any one of the following statements.
sp_helpindex EmployeeExecute sp_helpindex Employee
Can we create multiple clustered indexes in a table in SQL Server?
It is not possible to create more than one clustered index in a table in SQL Server. The Employee table that we have just created already has one clustered Index on the Id column which already determines the physical storage order of the data in the table.