Can anyone explain this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can anyone explain this?

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-10 : 06:07:54

Hi

I am looking at work created by a previous developer and wonder why they would do this with a view. First they have created a view like this:


CREATE VIEW MyView
 AS
 
 select 
	isnull(Col1,'') as Col1
	,isnull(Col2,'') AS Col2
	,isnull(Col3,'') AS Col3
	,isnull(Col4,'') AS Col4
	,COALESCE(SUM(Col5), 0) as Col5
	,ISNULL(Col6,'') AS Col6
 FROM 	Table1
	Group By 
	Col1
	,Col2
	,Col3 
	,Col4
	,Col5
        ,Col6
 
 GO

Then after that they have:

select 
	isnull(Col1,'') as Col1
	,isnull(Col2,'') AS Col2
	,isnull(Col3,'') AS Col3
	,isnull(Col4,'') AS Col4
	,COALESCE(SUM(Col5), 0) as Col5
	,ISNULL(Col6,'') AS Col6
 FROM 	MyView
	Group By 
	Col1
	,Col2
	,Col3 
	,Col4
	,Col5
        ,Col6
 
 GO

Note: I put the coalesce in as part of some upgrading work.

And the only difference I can see is the select from the view returns 2 less rows than the actual select from the table used to create the view.

I'm really not sure why someone would do this and what it would achieve, and can't figure out why the select from the view is 2 less rows than the table select, I didn't think it would make any difference.

Thanks

G

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-10 : 07:04:26

Looking at the code, I don't see any reason for the ISNULL's and Group By's in the select. May just have been that the select was written first against the table and view was inserted later.

The select from the view can have fewer rows than select from the table if there is more than one row for a combination of the columns in the group by clause. In your example, if there were two rows that had the same values in Col1 through Col6, this can happen.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-10 : 07:10:15

I don't believe that Col5 is in an aggregate function and also in the GROUP BY clause.
So there is at least one mistake in your post.
Because you are not posting the original statements and we have no example data it is not possible to give an answer...

No, you're never too old to Yak'n'Roll if you're too young to die.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-10 : 07:56:08

quote:
Originally posted by webfred

I don't believe that Col5 is in an aggregate function and also in the GROUP BY clause.

No that wasn't meant be there but the rest still stands as it is.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-10 : 08:11:51

1.
There is no need to do the GROUP BY again if the VIEW is created with exactly that GROUP BY.
2.
The less 2 rows are the result of the null handling. The VIEW is grouping NULLs but returning NO NULLs. The SELECT from the VIEW then hasn't any NULLs to handle so the result can be different.
But again: it is not needed to do the same GROUP BY again on already grouped rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-10 : 09:15:45

quote:
Originally posted by webfred

1.
There is no need to do the GROUP BY again if the VIEW is created with exactly that GROUP BY.
2.
The less 2 rows are the result of the null handling. The VIEW is grouping NULLs but returning NO NULLs. The SELECT from the VIEW then hasn't any NULLs to handle so the result can be different.
But again: it is not needed to do the same GROUP BY again on already grouped rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

I wonder also why you would do a sum twice in the same column, in that case you would need the group by. There is no difference in the results from what I see - now, same amount of rows so kind of strange. This is when I create the view then select only from the view without sum or coalesce, but even with sum and coalesce it is still same amount of rows.

G

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources