Additional thing you can try
SET @sSearchVal = 'sun%'
select myFields from myTable where myFirstField like @sSearchVal or myFirstField LIKE '%' + sSearchVal
union ....
>I have a table called AlertData with 500,000 records in it. This query is taking 11 seconds, which is too long for the client.
>
>
>DECLARE @sSearchVal NVARCHAR(50)
>SET @sSearchVal = '%sun%'
>
>SELECT RowId, ContractNo, SystemId, ItemNumber, Description, Price
> FROM AlertData
> WHERE ContractNo LIKE @sSearchVal OR
> ItemNumber LIKE @sSearchVal OR
> Description LIKE @sSearchVal
>
>
>
>There are indexes on all 3 fields.
>
>Thanks
>
>Kevin
If it's not broken, fix it until it is.
My Blog