Why is index scan run on outer join when not using "SELECT TOP"
If I run the following query **without** "TOP" in the Select clause then a index scan is run against table tBookingGuest. When I run the query with "TOP(10)" the optimizer chooses to do an index seek against tBookingGuest with the proper covering index. Why would the optimizer behave this way? SELECT br.bookingid, br.roomnumber, bg.adults, bg.children, bg.infants FROM tBookingRatePlan br inner join booking b on b.bookingid=br.bookingid and b.propertyid = @lpropertyid left join tBookingGuest bg on bg.bookingid = br.bookingid and bg.roomnumber = br.roomnumber group by br.bookingid, br.roomnumber, bg.adults, bg.children, bg.infants ---------------------- Here is some schema information **tBookingRatePlan** id int identity PK bookingid int FK to Booking table roomnumber int **Booking** BookingId int PK PropertyId int **tBookingGuest** Id int PK BookingId int RoomNumber int Adults int Children int Infants int *This table has non clustered index (BookingId,RoomNumber includes:adults,children,infants)
The optimiser may be deciding that an index seek is 'less costly' for the TOP 10, rather than the index scan for the full results. Also with a Top 10, you are asking the engine to do less work, and without an ORDER BY - simply return 10 'random' records. It's always quicker to give someone 10 random records than 100 specific ones. How many rows are returned when not limited by the TOP 10? As with any optimizer question - have you updated your index statistics?