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.

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…

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.

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.

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’

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.