|
|
Posted by Marcin A. Guzowski on 05/11/07 20:58
bbcworldtour@hotmail.com wrote:
> (Pardon me for asking a very basic question. I have come back to SQL
> Server after five years of Oracle, and my memory is a tad rusty).
>
> I have a bit of SHOW_PLAN output that I believe that I understand, but
> I would appreciate your comments if I am wrong.
>
> I have a requirement to produce a list of the primay key values in a
> table along with the total count of rows:
>
> Given this table and contents:
> (..)
> (don't wonder why - there's a SAS application on top which means that
> ordinary rules of logic don't apply)
> This is the query that I'm using:
>
> select ID
> , cnt.counter
> from taCountDemo
> cross join
> (select counter
> from ( select count(*) as counter
> from taCountDemo
> ) as i
> ) as cnt;
What about:
SELECT ID, (SELECT Count(*) FROM taCountDemo) as counter
FROM taCountDemo
?
Execution plan will be exactly the same but IMHO it looks much simpler.
> The SHOW_PLAN output is like this:
>
> |--Nested Loops(Inner Join)
> |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,
> [Expr1009],0)))
> | |--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
> | |--Clustered Index Scan(OBJECT:([master].[dbo].
> [taCountDemo]. )
> |--Clustered Index Scan(OBJECT:([master].[dbo].
> [taCountDemo].)
>
> My understanding of this is that the count(*) is only executed ONCE,
> and that the nested loop then combines the result (EXPR1006) with all
> keys obtained by scanning the primary key index. Or in other words
> that the single-"row" result of the count(*) is chosen as the "table"
> that drives the loop.
>
> Is that correctly understood?
Yes, you're completely right.
--
Best regards,
Marcin Guzowski
http://guzowski.info
Navigation:
[Reply to this message]
|