I have my master patient table, it has a little over 1 million records. When doing a patient lookup, I utilize the soundex() function of sql server 2000. I was wondering if it is possible to index based on that. If I don't use it, and I type smith, I get 253 records back in under a sec. If I use the soundex search I get 884 records back in about 2 seconds. I realize a lot of the time difference is bring the result set back (I would guess). So, if I can't index, is there a way to structure the query where maybe it would utilize both the soundex and the difference feature?


Here is the current query:

declare @tcLastName varchar(25)
declare @ttStart datetime

set @ttstart= '12/16/2002 08:00:00'
set @tcLastName='SMITH'

SELECT dbo.patients.*, 
       (select dbo.corooms.roomname  from dbo.meetings 
	 inner join dbo.corooms on dbo.meetings.roomid=dbo.corooms.roomid 
          	 where @ttStart  between meetings.begintime and meetings.endtime 
	and meetings.patientid=dbo.patients.patientid and meetings.iscancelled=0) as RoomName 
	
FROM   dbo.patients 
where SOUNDEX(lastname) like soundex(@tcLastName) 
or lastname like @tcLastName
order by lastname, firstname

Thanks for any input
Kirk