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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Periods(PeriodQuarter INT,PeriodYear INT)
INSERT Periods VALUES (1,2009)
INSERT Periods VALUES (2,2009)
INSERT Periods VALUES (3,2009)
INSERT Periods VALUES (4,2009)
INSERT Periods VALUES (1,2010)
INSERT Periods VALUES (2,2010)
INSERT Periods VALUES (3,2010)
GO
CREATE CLUSTERED INDEX ix_Periods on Periods(PeriodYear,PeriodQuarter)
GO
CREATE TABLE Periods(PeriodQuarter INT,PeriodYear INT)
INSERT Periods VALUES (1,2009)
INSERT Periods VALUES (2,2009)
INSERT Periods VALUES (3,2009)
INSERT Periods VALUES (4,2009)
INSERT Periods VALUES (1,2010)
INSERT Periods VALUES (2,2010)
INSERT Periods VALUES (3,2010)
GO
CREATE CLUSTERED INDEX ix_Periods on Periods(PeriodYear,PeriodQuarter)
GO

When we do this simple select query

T-SQL
1
2
SELECT *
FROM Periods
SELECT *
FROM Periods

We get the following table.

PeriodQuarter PeriodYear
1 2009
2 2009
3 2009
4 2009
1 2010
2 2010
3 2010

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

PeriodQuarter PeriodYear
1 2009
2 2009
3 2009

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.

T-SQL
1
 SELECT DATEADD(qq,1-1,DATEADD(yy,2009 -1900,0))
 SELECT DATEADD(qq,1-1,DATEADD(yy,2009 -1900,0))

Query 1
Here is the first query:

T-SQL
1
2
3
4
5
6
7
8
9
10
declare @startDate datetime
declare @endDate datetime
 
select @startDate = '20090101',@endDate = '20090928'
 
SELECT *
FROM Periods
WHERE  DATEADD(qq,PeriodQuarter-1,DATEADD(yy,PeriodYear -1900,0)) 
BETWEEN @startDate AND @endDate
GO
declare @startDate datetime
declare @endDate datetime

select @startDate = '20090101',@endDate = '20090928'

SELECT *
FROM Periods
WHERE  DATEADD(qq,PeriodQuarter-1,DATEADD(yy,PeriodYear -1900,0)) 
BETWEEN @startDate AND @endDate
GO

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.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
declare @startDate datetime
declare @endDate datetime
 
select @startDate = '20090101',@endDate = '20090928'
 
SELECT *
FROM Periods
WHERE  PeriodYear between YEAR(@startDate) and YEAR(@endDate)
AND DATEADD(qq,PeriodQuarter-1,DATEADD(yy,PeriodYear -1900,0)) 
BETWEEN @startDate AND @endDate
GO
declare @startDate datetime
declare @endDate datetime

select @startDate = '20090101',@endDate = '20090928'

SELECT *
FROM Periods
WHERE  PeriodYear between YEAR(@startDate) and YEAR(@endDate)
AND DATEADD(qq,PeriodQuarter-1,DATEADD(yy,PeriodYear -1900,0)) 
BETWEEN @startDate AND @endDate
GO

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

T-SQL
1
2
3
4
5
6
7
SELECT *
FROM Periods
WHERE
       PeriodYear BETWEEN YEAR(@startdate) AND YEAR(@enddate)
       AND PeriodYear * 4 + PeriodQuarter
               BETWEEN Year(@startdate) * 4 + DATEPART(Quarter, @startdate)
               AND Year(@enddate) * 4 + DATEPART(Quarter, @enddate)
SELECT *
FROM Periods
WHERE
       PeriodYear BETWEEN YEAR(@startdate) AND YEAR(@enddate)
       AND PeriodYear * 4 + PeriodQuarter
               BETWEEN Year(@startdate) * 4 + DATEPART(Quarter, @startdate)
               AND Year(@enddate) * 4 + DATEPART(Quarter, @enddate)

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

T-SQL
1
2
ALTER TABLE Periods ADD PeriodDate AS DATEADD(qq,PeriodQuarter-1,DATEADD(yy,PeriodYear -1900,0))  
GO
ALTER TABLE Periods ADD PeriodDate AS DATEADD(qq,PeriodQuarter-1,DATEADD(yy,PeriodYear -1900,0))  
GO

Now, when we query the table

T-SQL
1
2
SELECT *
FROM Periods
SELECT *
FROM Periods

… the data looks like this

PeriodQuarter PeriodYear PeriodDate
1 2009 2009-01-01 00:00:00.000
2 2009 2009-01-01 00:00:00.000
3 2009 2009-01-01 00:00:00.000
4 2009 2009-01-01 00:00:00.000
1 2010 2010-01-01 00:00:00.000
2 2010 2010-01-01 00:00:00.000
3 2010 2010-01-01 00:00:00.000

Now the query is much simpler

T-SQL
1
2
3
4
5
6
7
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
 
SELECT @startDate = '20090101',@endDate = '20090928'
SELECT *
FROM Periods
WHERE PeriodDate BETWEEN @startDate AND @endDate
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SELECT @startDate = '20090101',@endDate = '20090928'
SELECT *
FROM Periods
WHERE PeriodDate BETWEEN @startDate AND @endDate

|–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.

T-SQL
1
2
CREATE INDEX ix_PeriodDate ON Periods(PeriodDate)
GO
CREATE INDEX ix_PeriodDate ON Periods(PeriodDate)
GO

Now, if we run the same query again.

T-SQL
1
2
3
4
5
6
7
8
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
 
SELECT @startDate = '20090101',@endDate = '20090928'
 
SELECT *
FROM Periods
WHERE PeriodDate BETWEEN @startDate AND @endDate
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SELECT @startDate = '20090101',@endDate = '20090928'

SELECT *
FROM Periods
WHERE PeriodDate BETWEEN @startDate AND @endDate

|–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