Posts
1150
Comments
891
Trackbacks
1
Index/Search Tests for SQL

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

posted on Tuesday, February 15, 2005 11:48 AM Print
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 1 and 2 and type the answer here: