# Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

## A Quick Lesson on SQL Indexes

Just a quick post of some benchmarking code for our good friend Guayo at TheDailyWTF, in regards to this discussion.

The debate is, essentially: Assuming everything is properly indexed, which is faster/more efficient?

A) select * from …
WHERE DATEPART( … ) = @X and DATEPART( …) = @Y

B) select * from …
WHERE SomeDate BETWEEN @A and @B

C) select * from … inner join SomeLookupTable on ….
WHERE SomeLookUptable.X = @X and SomeLookupTable.Y = @Y

see for yourself ….. and enjoy the thread on the other site as well, for those who are curious.

Basically, below, we have:

Days – A lookup table of all possible dates our database needs to consider (1 row per date), along with a indexed Year and WeekNo columns
Test – A table of data covering many days within that date range, from which we will be querying for a specific week number and year.

create table Test /o:p

(ID int primary key, TestDate datetime) /o:p

/o:p

create index dt_index on Test (TestDate) /o:p

/o:p

create table Days /o:p

(DateVal datetime primary key, [WeekNo] int, [year] int) /o:p

/o:p

create index days_wd on Days ([WeekNo]) /o:p

create index days_yr on Days ([Year]) /o:p

/o:p

go /o:p

/o:p

declare @i int; /o:p

declare @d datetime; /o:p

/o:p

set @i=0 /o:p

set nocount on /o:p

while @i < 1000 /o:p

begin /o:p

insert into Test (ID,TestDate) values (@i, convert(datetime, '1/1/2000') + (@i/4)) /o:p

set @d = convert(datetime,'1/1/2000') + @i /o:p

insert into Days (DateVal, [WeekNo], [year]) values (@d, datepart(ww,@d), Year(@d))   /o:p

set @i=@i+1 /o:p

end /o:p

set nocount off /o:p

– Here's the slowest: /o:p

select * from Test /o:p

where Datepart(ww,TestDate) = 14 and DatePart(yy,testdate) = 2000 /o:p

/o:p

– Here's the fastest /o:p

select * from Test where TestDate between '3/26/2000' and '4/1/2000' /o:p

– Here's the second fastest (twice as fast as the first): /o:p

select Test.* from test /o:p

inner join Days on Test.TestDate = Days.dateval /o:p

where Days.WeekNo = 14 and Days.Year = 2000 /o:p

/o:p

go /o:p

drop table Days /o:p

drop table Test