This article explains how to use INLCUDE clause while creating the non-clustered index and how it helps to improve the query performance and will analyze the SQL performance by using sql server database perforamce monitoring tools.
Non-clustered index is used to
improve the performance of queries which are not covered by clustered index and
while creating of non-clustered index, we consider only those columns which are
being part of WHERE/ORDER BY/GROUP BY/JOIN statement.
CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]
(
[DepartmentCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [Primary]
In below SQL query, which fetches
department information like name and location on based on input department code.
Select DepartmentName, DepartmentLocation From dbo.Department where DepartmentCode = @Code
In this query, the department
record is quickly found with help of nonclustred index ‘DepartmentCode_IDX’ but
to get department name and location information they have to fetch from table.
Built Non-Clustered Index-tree
(B-Tree):
To improve the further
performance of above select query, we can use INCLUSE clause in non-clustered
index and specify the list of non-key columns which are part of Select
statement like ‘ DepartmentName’, ‘DepartmentLocation’
CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]
(
[DepartmentCode] ASC
) INCLUDE( DepartmentName, DepartmentLocation)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [Primary]
Built Non-Clustered Index-tree
(B-Tree):
You can see the Include Columns
are not part of all Index nodes expects leaf nodes so overall it is not
increasing index tree’s size and benefit to have include columns, it adds the
extra data at leaf node and it helps to get select columns value from index
leaf nodes instead of fetching data from table so in this way it improves the
performance of query.
Other related Post:
No comments:
Post a Comment