Indexing SQL for Between query with only one match?

between, indexing, sql

Solution

I think I have found the answer. I had to start by creating an Unique Clustered Index on Col1, then create an Unique Unclustered Index on Col2. The query then had to be updated to force lookups on each Index.

select * from table1 where Col1 = 
    (select max(Col1) from table1 where Col1 <= 8)
and Col2 = 
    (select min(Col2) from table1 where Col2 >= 8)

Execution plan now reports 0.0098 cost and 1 row handled.

Problem

We have a table with more than two million rows where all queries against it will be a Between lookup using `Column1` and `Column2`. Also, there will only be one possible result. For example... ``` Col1 Col2 1 5 6 10 11 15 select * from table1 where 8 between Col1 and Col2 ``` I currently have an unique clustered index on `Col1` and `Col2`. So far I have been unable to figure out how to further tune the query and the indexes to minimize the rows handled. The execution plan currently reports cost of almost 0.5 and 113k rows handled when locating the one and only correct answer. What options might I be overlooking? As requested, some details from the current execution plan: ``` Operation Clustered Index Seek Predicate CONVERT_IMPLICIT(bigint,[@2],0)<=[Col2] Seek Predicate Seek Keys[1]: End: Col1 <= Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0)) ```

Original source