create table Test
(ID int primary key, TestDate datetime)
create index dt_index on Test (TestDate)
create table Days
(DateVal datetime primary key, [WeekNo] int, [year] int)
create index days_wd on Days ([WeekNo])
create index days_yr on Days ([Year])
go
declare @i int;
declare @d datetime;
set @i=0
set nocount on
while @i < 100000
begin
insert into Test (ID,TestDate) values (@i, convert(datetime, '1/1/2000') + (@i/4))
set @d = convert(datetime,'1/1/2000') + @i
insert into Days (DateVal, [WeekNo], [year]) values (@d, datepart(ww,@d), Year(@d))
set @i=@i+1
end
set nocount off
-- Here's the slowest:
select * from Test
where Datepart(ww,TestDate) = 14 and DatePart(yy,testdate) = 2000
-- Here's the fastest
select * from Test where TestDate between '3/26/2000' and '4/1/2000'
-- Here's the second fastest (twice as fast as the first):
select Test.* from test
inner join Days on Test.TestDate = Days.dateval
where Days.WeekNo = 14 and Days.Year = 2000
go
drop table Days
drop table Test