OK, I gave this a try on my data and it works pretty well. With a table of about 3 million rows, the query is executing in about 2...

I haven't tried it yet. I'll let you know once I have.

Thanks for the idea!

I have not seen this case happen yet. The data comes from a an outside source so it wouldn't surprise me if this eventually happens. But it isn't...

I didn't. Mainly because the CTE idea only works with smaller datasets (less than 32767 rows).

Thanks for all of the suggestions!

I came up with another option using a recursive CTE. Here's how I did it...

declare @rangeTable table (start int, [end] int)

insert @rangeTable

...

Only 1 or 2 rows are going to be returned. In the actual query there is also an additional column I'm filtering on. Here's the query I originally...

Well, this was working very well for a while (almost 2 months!). However I hit the potential problem that was mentioned earlier with needing to adjust the range. ...

This looks pretty good! I'll test it out on the real dataset and see how it goes. I did simplify the code a little. Here's what I'm...

Oops, guess I spoke too soon. It's close but there's one more thing to take care of.

The previous solution will give these results

IDBEGINENDMULT1MULT2MULT3MULT4

1112319900501207812314500450045004500 -- (Code 40)

1112320050701200609305500550055005500...

That's excellent!!! Thank you!

I've tried something similar without much luck. Thanks for trying!

I'm now playing around with CTEs to see if that will work. Anybody else have any suggestions?

Thanks!

OK, trying again...

Here's some SQL to get everything setup

declare @table table(ID int,

...

The entire stored procedure takes ~450 ms. When I look at the actual execution plan in SMS, there are 7 queries in the batch and this 1 query is...

this one

WHERE LOW <= 982827279

AND LOW > 982827279 -1000

AND HIGH >= 982827279

Excellent! I'm now getting a duration of 1 ms and with only 3 reads!!

Could you explain what's going on here to make it so much faster?

Thanks!