469,129 Members | 1,725 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,129 developers. It's quick & easy.

Reading SHOW_PLAN output

(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:

create table taCountDemo (ID char(01) primary key, someData
varchar(50));

insert into taCountDemo (ID,somedata)
select 'a', 'aaaa'
union
select 'b', 'bbbb';

the results should be:

ID counter
---- -----------
a 2
b 2

(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;

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?

Thanks for your input

Bo Brunsgaard

May 11 '07 #1
2 1482
bb**********@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
May 11 '07 #2
SB
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.
Maybe I am wrong but I think it is other way around. It puts the sql
statement in a nested loop and count(*) is executed many times.

May 14 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by jeff M via .NET 247 | last post: by
8 posts views Thread by junk5 | last post: by
10 posts views Thread by Tyler | last post: by
21 posts views Thread by Naya | last post: by
1 post views Thread by anii | last post: by
21 posts views Thread by Stephen.Schoenberger | last post: by
2 posts views Thread by Logan Lee | last post: by
4 posts views Thread by radhikams | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.