Indexes are nothing but the lookup tables created on the tables to speed up the process of execution of the query.
For Ex: These lookup tables are similar to the Index table in the books which are used to reference the chapter in the book whereas the lookup table on the table in a database is created to refer data records
Indexes are used in SQL to speed up the process of retrieval of the data from the database by accelerating the speed of execution of Select query and where clause.
It can be created and deleted without causing any effect on the source of index i.e table.
If we run a select query over a particular table the way in which it retrieves the data is called Table Scan
A table scan is nothing but reading data start to end in a sequential order
For Ex: If table as if a company DB as employee table
which as Empid from 101 to 200 then it will start the scan each and every row 101 to 200 for a particular record
Whereas the Index used the method called index in which it creates index values which are like reference value to the actual table to fetch the whole data based on that reference value i.e is the index
Which are arranged in a B-tree balanced tree from so the process of referencing is faster
Forex: If table as if a company DB as employee table which as Empid from 101 to 200 then it will divide the data into half as shown below 150 is at root node cause it divides data into two equal parts in left the numbers less than 150 will be passed and at right greater than 150
It goes on segregating data until it reaches the end of a data i.e. is called leaf node of Balanced Tree
Types of Indexes
There are two types of indexes there are as follows:
1.Clustered Index
- A clustered index is the index which is called sorted because it sorts the key values i.e the primary key or the unique key, in particular, order so that fetching data is easier
- If a select query is fired on the clustered index it goes through the index and searches through the indexes and then retrieves whole row data from the leaf base table
- A table with Primary key by default creates a clustered index
- Clustered Index is used to physically store the index memory in the sorted form which is in ascending and descending order.
- Only one Clustered Index on one table.
- A clustered index is suitable for the table which has heavy data modifications.
2.Non-Clustered Index
- The non clustered index is an index which is stored in the form of pointer which is used to reference the data
- The Non-clustered index does not store the index in a sorted form in the memory.
- These indexes can be created between 0 to 999 for a single table.
Demo
1.Script to create Employee table :
CREATE TABLE [dbo].[DimEmployee](
[EmployeeKey] [int] IDENTITY(1,1) NOT NULL,[ParentEmployeeKey] [int] NULL,[EmployeeNationalIDAlternateKey] [nvarchar](15) NULL,
[ParentEmployeeNationalIDAlternateKey] [nvarchar](15) NULL,
[SalesTerritoryKey] [int] NULL,[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,[MiddleName] [nvarchar](50) NULL,
[NameStyle] [bit] NOT NULL,[Title] [nvarchar](50) NULL,
[HireDate] [date] NULL,[BirthDate] [date] NULL,
[LoginID] [nvarchar](256) NULL,[EmailAddress] [nvarchar](50) NULL,
[Phone] [nvarchar](25) NULL,[MaritalStatus] [nchar](1) NULL,
[EmergencyContactName] [nvarchar](50) NULL,
[EmergencyContactPhone] [nvarchar](25) NULL,[SalariedFlag] [bit] NULL,
[Gender] [nchar](1) NULL,[PayFrequency] [tinyint] NULL,
[BaseRate] [money] NULL,[VacationHours] [smallint] NULL,
[SickLeaveHours] [smallint] NULL,[CurrentFlag] [bit] NOT NULL,
[SalesPersonFlag] [bit] NOT NULL,[DepartmentName] [nvarchar](50) NULL,
[StartDate] [date] NULL,[EndDate] [date] NULL,[Status] [nvarchar](50) NULL,
[EmployeePhoto] [varbinary](max) NULL
)
GO
2.I have inserted aroun 300 records in Employee table below there is a example of Insert query:
INSERT [dbo].[DimEmployee] ([EmployeeKey], [ParentEmployeeKey], [EmployeeNationalIDAlternateKey], [ParentEmployeeNationalIDAlternateKey], [SalesTerritoryKey], [FirstName], [LastName], [MiddleName], [NameStyle], [Title], [HireDate], [BirthDate], [LoginID], [EmailAddress], [Phone], [MaritalStatus], [EmergencyContactName], [EmergencyContactPhone], [SalariedFlag], [Gender], [PayFrequency], [BaseRate], [VacationHours], [SickLeaveHours], [CurrentFlag], [SalesPersonFlag], [DepartmentName], [StartDate], [EndDate], [Status], [EmployeePhoto]) VALUES (1, 18, N’14417807', NULL, 11, N’Guy’, N’Gilbert’, N’R’, 0, N’Production Technician — WC60', CAST(N’2000–07–31' AS Date), CAST(N’1976–05–15' AS Date), N’adventure-works\guy1', N’guy1@adventure-works.com’, N’320–555–0195', N’M’, N’Guy Gilbert’, N’320–555–0195', 0, N’M’, 1, 12.4500, 21, 30, 1, 0, N’Production’, CAST(N’2000–07–31' AS Date), NULL, N’Current’, 0xFFD8FFE000104A46494600010101004800480000FFED00D850686F74)GO
3.If we run the select this query with this statement Set Statistics io on for traces the logical reads, scan count,physical reads etc.
SET Statistics io on
SELECT *
FROM [Emp].[dbo].[DimEmployee]
Where EmployeeKey=’177' or LastName=’Earls’;
This output for the following query:
(2 rows affected)
Table ‘DimEmployee’. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob read-ahead reads 0.
4. To create Index on Table:
CREATE CLUSTERED INDEX Cus_index ON customer(CustomerKey)
5. If we run the same select query as of the above following will be the output
(2 rows affected)
Table ‘DimEmployee’. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob read-ahead reads 0.
The above outputs show how the index improved the performance of the select query the logical reads are less in the second select query which as index created on the table than the first query.
If you want to learn more about indexes go through this video:-
