Can a query use more than one nonclustered index for a table?

I have a query that looks like this

SELECT TOP 1000 C.iId_company
FROM dbo.Company AS C WITH(NOLOCK)
WHERE 
C.col_1 LIKE 'something%'
OR C.col_1 LIKE 'something2%'
OR C.col_1 LIKE 'something3%'
OR C.col_2 LIKE 'something%'
OR C.col_2 LIKE 'something2%'
OR C.col_2 LIKE 'something3%'

I tried to make that query faster, and I tried adding indexes on col_1 and col_2. If I comment out the conditions concerning col_2, the query is extra-fast, same thing for col_1 (if I comment out conditions about col_1). But when I leave it like that, it's same old story, very slow.

What I suspect from looki开发者_开发知识库ng at the execution plan, it's that only one index is used at a time and SQL performs a scan of the subset after the first index is used. I tried different things (making an index of both columns, but does not work also)

Actually my only solution would be to split the query and use a UNION. Is there a way to make that kind of query faster and keep it in one query ?

Use a union. That is:

SELECT TOP 1000 C.iId_company
FROM dbo.Company AS C WITH(NOLOCK)
WHERE 
C.col_1 LIKE 'something%'
OR C.col_1 LIKE 'something2%'
OR C.col_1 LIKE 'something3%'

union all 

SELECT TOP 1000 C.iId_company
FROM dbo.Company AS C WITH(NOLOCK)
WHERE 
C.col_2 LIKE 'something%'
OR C.col_2 LIKE 'something2%'
OR C.col_2 LIKE 'something3%'

Adjust as necessary (e.g. you may need to wrap that whole thing in a select so that you can add an order by clause to get whatever you consider to be the top 1000). But I think you'll be happy with this solution.

Your requirements point out a need for two separate indexes, one on each column you're searching. Use whatever DBA tool you prefer to generate and view the explain plan for the query. Now you can start re-working the query to see if the explain plan looks any better than your previous attempts. You may need to use a UNION or a common table expression to combine the two queries together into a single result set.