Using ISNULL forces index seek but COALESCE results in Index scan.  

Posted by ReelTym
November 03,
2011


DBCC FREEPROCCACHE
DBCC  DROPCLEANBUFFERS
set statistics io on

select BookingID, 
       DSClientID, 
       DSProductCatalogID, 
       DSBookingResponse 
from   dbo.BookingDestinationServices 
where  BookingID          = COALESCE( 38035, BookingID )

/*
Table 'BookingDestinationServices'.
Scan count 1,
logical reads 414,
physical reads 8,
read-ahead reads 458,
lob logical reads 40,
lob physical reads 2,
lob read-ahead reads 0.
*/

DBCC FREEPROCCACHE
DBCC  DROPCLEANBUFFERS
set statistics io on
select BookingID, 
       DSClientID, 
       DSProductCatalogID, 
       DSBookingResponse 
from   dbo.BookingDestinationServices 
where  BookingID          = ISNULL( 38035, BookingID )

/*
Table 'BookingDestinationServices'.
Scan count 0,
logical reads 2,
physical reads 1,
read-ahead reads 0,
lob logical reads 40,
lob physical reads 2,
lob read-ahead reads 0.
*/

This entry was posted on Thursday, November 03, 2011 and is filed under . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .
Filed under

0 comments

Newer Post Older Post