Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Arnold Fribble
Yak-finder General
1961 Posts |
 Posted - 2002-04-21 : 07:28:32
|
This is a way of getting the largest two pks from a table returned as a single row (Northwind example):SELECT o1, (SELECT MAX(OrderID) FROM Orders WHERE OrderID <> o1) AS o2FROM ( SELECT MAX(OrderID) o1 FROM Orders ) AS MaxOrder It's quite fast on SQL Server 2000 (sp2), since the second-largest value is looked up with an index seek that returns one value. It's probably easier to see the basic idea if you change the <> to < (which produces the execution plan you'd expect).However, with <>, the construction of the search condition (right of the nested loop on the execution plan) seems to be doing strange things! Where are the numbers 6 and 10 in the right-most two Compute Scalars coming from? How does this relate to the bitwise ANDs in the other Compute Scalars? And what is the execution operator Merge Interval doing? Can anyone see what's going on here?Edited by - Arnold Fribble on 04/21/2002 07:51:23 |
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-04-24 : 00:38:06
|
This stuff is child's play. What _I_ want to know is what's happening to the thermo-nuclear defribulator...I'm sorry, I just couldn't resist. I wish I could help you, but I really can't...  |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-04-24 : 04:59:13
|
| Yeah, I later discovered that many queries I'd already written using INFORMATION_SCHEMA views generated plans using Merge Interval: all the bit-flipping seems to be just part of the way that operator works. I'm sure it has something to do with the transperambulation of pseudo-cosmic antimatter. |
|
|
|
|
|
|
Bizarre execution plan using Merge Interval