I didn't try but I think you can create calculated column SOUNDEX(lastname) and index it. I'm not sure if SQL SERVER will use this index or not for your query. You'll have to test it.

>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