This article is follow up of my previous article SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup. Please do read my previous article before continuing further.
I have described there two different methods to reduce query execution cost. Let us compare the performance of the SELECT statement of the previous query.
We have created two different indexes on the table.
Method 1: Creating covering non-clustered index.
In this method, we will create a non-clustered index that contains the columns used in the SELECT statement along with the column used in the WHERE clause.
CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover] ON [dbo].[OneIndex] (
City, FirstName, ID
) ON [PRIMARY]
GO
Method 2: Creating included column non-lustered index.
In this method, we will create a nonclustered index that includes the columns used in the SELECT statement along with the column used in the WHERE clause. Here, we will use new syntax introduced in SQL Server 2005. An index with included nonkey columns can significantly improve query performance when all the columns in the query are included in the index.
CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex] (
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO
Let us compare the performance of our query by running the following query with two different indexes.
SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Cover))
WHERE City = 'Las Vegas'
GO
SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Include))
WHERE City = 'Las Vegas'
GO
Let us examine the execution plan and compare the query costs.
In fact, in this example, the performance of both the queries is quite same. In this case, we can use either of the query and obtain the same performance.
I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.
1) Index can exceed the 900-byte limitation of the index key.
2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.
3) Size of the key index can be reduced, which improves the overall performance of the index operation.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Related Post:
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
13 Comments. Leave new
Hi Pinal,
Is it good to create one of the above index when we are using 20+ columns in select statement?
hi every one,
I need a query, and the requirement is–
Count the number of customers that shopped 10 months ago, but have not returned since.
(example: For October , count total number of customers that shopped in January. Then count the number of those customers that DID not return Feb-Sept. )
My table name is customerInvoices, consists invoiceId, customerId and invoiceDate.
I wrote the fallowing query for the october month( means the customer should have an invoice in january and he should not have any invoice in the next 8 months(feb-sep)).
— To get lapsed customers of october
select month(GETDATE())AS [Month],count(ci.customerId) AS [Number of Lapsed customers] from CustomerInvoices ci
where YEAR(ci.invoiceDate)=2009
and month(ci.invoiceDate)=MONTH(DATEADD(month,-09,getdate()))
and customerID not in
(
select customerID from customerInvoices
where (invoiceDate)>
( CAST(
(CAST
(month(DATEADD(MONTH,-08,GETDATE())) AS CHAR(2))
+’-01-‘+
CAST(YEAR(ci.invoiceDate) AS CHAR(4))) AS date)
)
and (invoiceDate)<
(
CAST(
(CAST
(month(DATEADD(MONTH,-01,GETDATE())) AS CHAR(2))
+'-01-'+
CAST(YEAR(ci.invoiceDate) AS CHAR(4))) AS date)
)
)
It's working perfectly for the october month, but i want to display the result from january to october, how can i include other months also…
If anyone knows this plz help,
thank you.
@Rajendra
What I understand upto reading 1 time only is
select distinct month from the year 2009 (january to this month)
and then use CURSOR…
I think this might help you..
Hi Pinal,
Great post, but how do i remove Bookmark lookup (table with clustered index) where it is not possible create index with include columns. Or is it possible?
thanks!!
In addition, the applicant participates in multiple national, state, and local associations to gather and share best practices and learning. ,
I need some help…. I am not a DBA, and am just getting into this SQL thing. I have a query established, but it is returning HTML characters in two columns. How can you eliminate that inside of the query. I have pasted the query below for your review.
SELECT vWorkOrderReportRandle.WOOID, vWorkOrderReportRandle.WODateScheduled, vWorkOrderReportRandle.EquipDesc, vWorkOrderReportRandle.WOStatusDesc, vWorkOrderReportRandle.Type, vWorkOrderReportRandle.WOProblem, vWorkOrderReportRandle.WORepairsRequired, vWorkOrderReportRandle.WOLaborHoursEst, vWorkOrderReportRandle.WOCostAct, vWorkOrderReportRandle.WOCostEst, vWorkOrderReportRandle.WOKit, vWorkOrderReportRandle.Initiator, vWorkOrderReportRandle.Assignee, vWorkOrderReportRandle.WOStatus, vWorkOrderReportRandle.Requestor, vWorkOrderReportRandle.FullPath
FROM HamptonMaintenance.dbo.vWorkOrderReportRandle vWorkOrderReportRandle
I wanted to ask one question that is does the Include options to create covering index actually i.e. physically create non-clustered index on the column.
Hi,
I’m not sure if I understood correctly. Did you meant…
CREATE NONCLUSTERED INDEX IX_Foo ON FOO (bar) INCLUDE (baz)
…that is there now a nonclustered index created on “baz” column?
No, there isn’t. Index is created only on “bar” column. It’s just plain normal nonclustered index.
But the data that’s in “baz” column is copied to TEMPDB and any read operation, that uses IX_Foo index, is done againts TEMPDB. If IX_Foo index is not used then the “baz” column is read from the actual data table.
Hi,
This is an awesome article. I came across to search method of non-clustered index on Google and I find this article on top. I obtained exact needs in this and now soon, I will be practical.
Thanks for providing such useful information.
I have one question is index size smaller in method 2?
My question is its write to make one of the above index when we are using 10+ columns in particular statement?
Why I cant remove Key Lookup? I am using SQL Sever 2008.
I have a Document table and create a index as
CREATE NONCLUSTERED INDEX [DOCUMENT_MPSACCOUNT_FK] ON [dbo].[DOCUMENT]
(
[MPSACCOUNTID] ASC
)
include (documentID,documenttypeid)
GO
The query plan shows it is still doing Key lookup for documentID and documenttypeid after Index Seek on INDEX [DOCUMENT_MPSACCOUNT_FK], why?
SELECT DATEPART(YEAR,SQ1.InvoiceDate) AS ‘YEAR’ , DATEPART(MONTH,SQ1.InvoiceDate) AS ‘MONTH’, COUNT(DISTINCT SQ1.CustomerID) AS ‘CustCount’
FROM (
SELECT A.*, LAG(A.InvoiceDate,1,0) OVER (PARTITION BY A.CustomerID ORDER BY A.InvoiceDate) AS PrevDate
FROM CustomerInvoices A
) SQ1
WHERE SQ1.PrevDate ‘1900-01-01 00:00:00.000’
AND DATEDIFF(MONTH,SQ1.PrevDate, SQ1.InvoiceDate) >= 3
GROUP BY DATEPART(YEAR,SQ1.InvoiceDate) , DATEPART(MONTH,SQ1.InvoiceDate)
ORDER BY ‘YEAR’