Hot answers tagged


The aggregate is a scalar aggregate (no group by clause). These are defined in SQL Server to always produce a row, even if the input is empty. For a scalar aggregate, MAX of no rows is NULL, COUNT of no rows is zero, for example. The optimizer knows all about this, and can transform an outer join into an inner join in suitable circumstances. -- NULL for a ...


The thing to remember here is that execution plans suck the data through. So the Nested Loop operator calls the Stream Aggregate 4 times. The Stream Aggregate calls the Filter 4 times as well, but only gets a value twice. So the Stream Aggregate gives four values. Twice it gives a value, and twice it gives Null.


Execution plan still shows ClusterIndex Seek why? The initial seek down the b-tree is to find the first row where CustomerID >= 1. From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon ...


The answer would be simple here, assuming you just have one clustered index on table and your query is like select * from customer where CustomerID between 1 and 70000 In above case seek would be preferred by optimizer because first the index would search the data based on condition CustomerID=1 and would find the first row which matches the predicate. ...

Only top voted, non community-wiki answers of a minimum length are eligible