Someone posted a question, they wanted to return the quarters and years within a range that were passed in. The problem they had is that they stored this data in a year and a quarter column. The table looked like this

When we do this simple select query

We get the following table.

If we pass in a range from 2009-01-01 until 2009-09-28, then the following data should be returned:

Running the queries

There are a couple of ways to return that data - below are 3 queries and their execution plans

The first query creates a date from the 2 columns and then checks if that date falls between the end and start date passed in.
Run the statement below, it returns '2009-01-01 00:00:00.000'. Mess around with the numbers to to see how it works.

Query 1
Here is the first query:

This is the execution plan, as you can see it uses a Clustered Index Scan

|--Clustered Index Scan(OBJECT:([msdb].[dbo].[Periods].[ix_Periods]),
WHERE:(dateadd(quarter,[msdb].[dbo].[Periods].[PeriodQuarter]-(1),
dateadd(year,[msdb].[dbo].[Periods].[PeriodYear]-(1900),'1900-01-01 00:00:00.000'))>=[@startDate]
AND dateadd(quarter,[msdb].[dbo].[Periods].[PeriodQuarter]-(1),
dateadd(year,[msdb].[dbo].[Periods].[PeriodYear]-(1900),'1900-01-01 00:00:00.000'))<=[@endDate]))

Query 2
Query 2 is a little smarter, it checks for the year which is the first key in the composite clustered index and thus avoids a Clustered Index Scan like the query above.

Here is the plan, as you can see it results in a Clustered Index Seek.

|--Clustered Index Seek(OBJECT:([msdb].[dbo].[Periods].[ix_Periods]),
SEEK:([msdb].[dbo].[Periods].[PeriodYear] >= datepart(year,[@startDate])
AND [msdb].[dbo].[Periods].[PeriodYear] <= datepart(year,[@endDate])),
WHERE:(dateadd(quarter,[msdb].[dbo].[Periods].[PeriodQuarter]-(1),
dateadd(year,[msdb].[dbo].[Periods].[PeriodYear]-(1900),'1900-01-01 00:00:00.000'))>=[@startDate]
AND dateadd(quarter,[msdb].[dbo].[Periods].[PeriodQuarter]-(1),
dateadd(year,[msdb].[dbo].[Periods].[PeriodYear]-(1900),'1900-01-01 00:00:00.000'))<=[@endDate]) ORDERED FORWARD)

Query 3
Query 3 is very similar to query 2 but instead of dateadd it uses arithmetic to grab the correct rows

Here is the plan, as you can see it results in a Clustered Index Seek also.

|--Clustered Index Seek(OBJECT:([msdb].[dbo].[Periods].[ix_Periods]),
SEEK:([msdb].[dbo].[Periods].[PeriodYear] >= datepart(year,[@startDate])
AND [msdb].[dbo].[Periods].[PeriodYear] <= datepart(year,[@endDate])),
WHERE:(([msdb].[dbo].[Periods].[PeriodYear]*(4)+[msdb].[dbo].[Periods].[PeriodQuarter])
>=datepart(year,[@startDate])*(4)+datepart(quarter,[@startDate])
AND ([msdb].[dbo].[Periods].[PeriodYear]*(4)+[msdb].[dbo].[Periods].[PeriodQuarter])
<=datepart(year,[@enddate])*(4)+datepart(quarter,[@endDate])) ORDERED FORWARD)

Another approach

Instead of converting and doing arithmetic, you could add a computed column to the table

Now, when we query the table

... the data looks like this

Now the query is much simpler

|--Clustered Index Scan(OBJECT:([msdb].[dbo].[Periods].[ix_Periods]), WHERE:(dateadd(quarter,[msdb].[dbo].[Periods].[PeriodQuarter]-(1),dateadd(year,[msdb].[dbo].[Periods].[PeriodYear]-(1900),'1900-01-01 00:00:00.000'))>=[@startDate] AND dateadd(quarter,[msdb].[dbo].[Periods].[PeriodQuarter]-(1),dateadd(year,[msdb].[dbo].[Periods].[PeriodYear]-(1900),'1900-01-01 00:00:00.000'))<=[@endDate]))

So we still have an index scan, but if we create an index on the computed column now, we can find out if that helps.

Now, if we run the same query again.

|--Index Seek(OBJECT:([msdb].[dbo].[Periods].[ix_PeriodDate]), SEEK:([msdb].[dbo].[Periods].[PeriodDate] >= [@startDate] AND [msdb].[dbo].[Periods].[PeriodDate] <= [@endDate]) ORDERED FORWARD)

And there is your index seek.

Just as an FYI, don't just start creating computed columns and adding indexes left and right. If you can, try to modify the table so that you store the dates only; after that it is easy with the datepart function to figure out what year, quarter or month is stored in that column.

To keep this post to a reasonable length, I decided to create another post that will show you how to use a calendar table to the same kind of query. That post will be up in a day or two.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum