August 23, 2016 at 8:33 pm
I know TSQL doesn't support tuples (e.g. WHERE (Col1,Col2) > (10,8)) but if I have an index as such
CREATE UNIQUE INDEX IX_IX ON Table (Col1, Col2)
and I want to select records over a range, greater than a specific set of values, what is the best way to accomplish this?
The best I can come up with is this
WHERE (Col1 = @Param1 AND Col2 > @Param2)
OR (Col1 > @Param1)
I was seeing if anyone had anything better
August 23, 2016 at 9:16 pm
I'd want to compare execution plans because this is clunkier lookin, but you might also try
SELECT ... WHERE Col1 = @Param1 AND Col2 > @Param2
UNION ALL
SELECT...WHERE Col1 > @Param1
If your first attempt is producing a table scan, this should be more efficient. You're really getting two index seeks. The first one gets everything for col1 = 10 and col2 > 8. The second gets you everything where col1 > 10.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 23, 2016 at 9:29 pm
Wait, I missed this.
CREATE UNIQUE INDEX IX_IX ON Table (Col1, Col2)
You're only skipping rows 1-8 where col1 = 10
Unless you go up to thousands of values in Col2, it really doesn't make much difference in time between a seek and a scan. But I just tested your original query against my UNION ALL suggestion, and your original query actually produces the most efficient query plan. Just a straightforward index SEEK, instead of the index scan I feared. Go for it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply