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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy