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 |
|
ElCapitan
Starting Member
28 Posts |
 Posted - 2009-03-06 : 04:38:18
|
| Hi all,How does SQL execute when using TOP 1. Specifically, lets say we have the common tblOrder table and tblOrderDetail table as a parent child relationship. For order 999, there are five items in the tblOrderDetail table.If my query is...SELECT TOP 1 * FROM tblOrder INNER JOIN tblOrderDetail ON tblOrder.OrderID=tblOrderDetail.OrderID WHERE tblOrder.OrderID=999What happens to the execution life cycle? Does it get the record where OrderID=999 then gets ALL the related records in tblOrderDetail then returns ONLY the TOP 1, OR, because we only ask for the TOP 1, once it gets the first matching related record does it then stop execution (much like EXISTS does when it finds the first 'true' result)?Thanks for your time. |
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-06 : 07:43:21
|
| It depends. Since you had no order by statement, the engine stops after grabbing the first row (as you postulate). If you had an order by, or a distinct\ group by (yeah, these wouldn't make sense for top 1, but for top 2+ it might) then the engine will have to get all the rows before applying the TOP clause. |
 |
|
|
ElCapitan
Starting Member
28 Posts |
Posted - 2009-03-06 : 08:33:14
|
| That sounds sensible. Thanks Aged Yak Warrior. |
|
|
|
|
|
|
Parent - Child TOP 1