You should be able to create a calculated column on the SOUNDEX() function and index it. SQL Server will use the index if you reference the column in your query:

CREATE TABLE temp (
lastName nvarchar(60)
,s_lastName AS SOUNDEX(lastName)
, ...
)

CREATE NONCLUSTERED INDEX temp_s_lastName ON temp(s_lastName)

SELECT * FROM temp WHERE s_lastName = ...

See the topic Creating Indexes on Computed Columns in the BOL. There are some configuration settings that MUST be set in order to create and maintain the index.

-Mike

>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