Hi Walter,

You're missing the point. SQL Server cannot use indexes at all because LIKE condition starts with wild card character ('%').

>
>SQL server has something called an index scan, which might be elected to be used in such cases. Index scans are faster than full table scans. Whether it does, depends on a number of factors like, row count of the table, the parameters (e.g. values where there are no wild cards used or at the end of the parameter, are optimizable), a chached execution plan and the statistics of the column. In this case the OR is one of the obsticals to handle the query in its most efficient way. Better to rewrite in 3 selected combined with two UNIONs and make sure the execution plan is recalculated eac time.
>
>
>
>>Your query is not optimizable and requires a table scan. You may consider using full-text search if you're searching for words not just substrings.
>>
>>>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