Accessing and changing data 2008

Question

Post reply

Accessing and changing data 2008

Ron McCullough

SSC Guru

Points: 63877
More actions
September 22, 2008 at 8:58 pm

#193379

Comments posted to this topic are about the item Accessing and changing data 2008
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 1

Either I read the BOL totally wrong, or you got the answer totally wrong.

The BOL (linked in your answer) says they are equivalent. Unless "equivalent" does not equal to "the same"?

Specifying GROUPING SETS ( [,...n ]) as the GROUP BY list is equivalent to a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. Aggregates on floating-point numbers might return slightly different results.
The following statements are equivalent:
SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))
SELECT customer, NULL as year, SUM(sales)
FROM T
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T
GROUP BY year

Urbis, an urban transformation company

sabinweb Hall of Fame Points: 3871 More actions · Answer 2

And how do I know that "sales" is a floating-point numbers ?

i guess ?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hey i thought the answer is "They are the same".

"Keep Trying"

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

According to the link provided in the answer, the query with the GROUPING SETS equals the query with the GROUP BY and UNION ALL.

The correct answer of QotD should be "both queries are the same".

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

liam.stirling Say Hey Kid Points: 689 More actions · Answer 5

I thought "The following statements are equivalent" meant they were the same.

Silly me!

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 6

Sorry to whine, but this question is a little disappointing - It's educational on at least two counts (grouping sets - new feature, and possible gotcha on floating point numbers), but ultimately what does it mean for two queries to be "the same"?

Do you judge based on the results? The query plan? The syntax?

Most people most of the time would be referring to "logical" equivalence - is the returned data set exactly the same regardless of the data set? And the answer to that question is, sadly, "It cannot be determined based on the information provided", as we would need to know the data structure of the table.

In case anyone reads this far, I do have a question: Do we know under what exact circumstances floating-point numbers (presumably Real and Float) will return different results? Can we assume Decimal types are exempt from this uncertainty?

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

daft SSC Eights! Points: 806 More actions · Answer 7

liam.stirling (9/23/2008)
I thought "The following statements are equivalent" meant they were the same.
Silly me!

Exactly. Even though the resultsets might be slightly different, the statements are equivalent. Boooo, I want my point back! 🙂

Also does this highlight an issue with floating point anomaly in grouping sets, or has this always been an issue with aggregate statements on floating points, ie a SQL Server bug?

liam.stirling Say Hey Kid Points: 689 More actions · Answer 8

Tao Klerks (9/23/2008)
Sorry to whine, but this question is a little disappointing - It's educational on at least two counts (grouping sets - new feature, and possible gotcha on floating point numbers), but ultimately what does it mean for two queries to be "the same"?
Do you judge based on the results? The query plan? The syntax?
Most people most of the time would be referring to "logical" equivalence - is the returned data set exactly the same regardless of the data set? And the answer to that question is, sadly, "It cannot be determined based on the information provided", as we would need to know the data structure of the table.
In case anyone reads this far, I do have a question: Do we know under what exact circumstances floating-point numbers (presumably Real and Float) will return different results? Can we assume Decimal types are exempt from this uncertainty?

I agree, Tao. There was an educational element to the question because I looked at something new, but there is also a semantic problem with the question, and even with reference to the BOL, I prefer your option of a third answer.

Richard M Karpel Ten Centuries Points: 1221 More actions · Answer 9

I went to the link posted as an explanation http://msdn.microsoft.com/en-us/library/bb510427.aspx

and the example listed at the top of the page is exactly the same as the question, and it says they are equivalent.

From the link above:

The following statements are equivalent:

Copy Code

SELECT customer, year, SUM(sales)

FROM T

GROUP BY GROUPING SETS ((customer), (year))

Copy Code

SELECT customer, NULL as year, SUM(sales)

FROM T

GROUP BY customer

UNION ALL

SELECT NULL as customer, year, SUM(sales)

FROM T

GROUP BY year

Rick Karpel

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

I would like to thank all who have commented. I have learned a little more to apply to any additional QOD that I may submit. Please note that I relied on the

Merriam-Webster Dictionary definition for the words equivalent, and same which are:

Equivalent:

equal in force, amount, or value

Same:

equal in size, shape, value, or importance —usually used with the or a demonstrative (as that, those) in all senses

The main objective as I understand the QOD is to introduce readers to new and/or little known aspects of SQL Server it is my hope that this is what this question did.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 11

bitbucket (9/23/2008)
I would like to thank all who have commented. I have learned a little more to apply to any additional QOD that I may submit. Please note that I relied on the
Merriam-Webster Dictionary definition for the words equivalent, and same which are:
Equivalent:
equal in force, amount, or value
Same:
equal in size, shape, value, or importance —usually used with the or a demonstrative (as that, those) in all senses
The main objective as I understand the QOD is to introduce readers to new and/or little known aspects of SQL Server it is my hope that this is what this question did.

As a non-native english speaker I'm unaware of such small differences between the meaning of words.

For me: Same == Equivalent :ermm:

But you are right about touching new aspects of SQL Server...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jerry Hung SSChampion Points: 12968 More actions · Answer 12

I want my point back too

so, is it the SAME, or not?

I initial thought was I missed the UNION vs UNION ALL, but then it did have UNION ALL, so they were the SAME

Anyway, at least I don't feel too bad about getting it "almost" correct

considering I just finished reading the Introducing Microsoft SQL Server 2008 eBook, and reading Brad's SQL 2008 PDF now as well

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 13

bitbucket (9/23/2008)
I would like to thank all who have commented. I have learned a little more to apply to any additional QOD that I may submit. Please note that I relied on the
Merriam-Webster Dictionary definition for the words equivalent, and same which are:
Equivalent:
equal in force, amount, or value
Same:
equal in size, shape, value, or importance —usually used with the or a demonstrative (as that, those) in all senses
The main objective as I understand the QOD is to introduce readers to new and/or little known aspects of SQL Server it is my hope that this is what this question did.

So you are admitting that the answer should be that they are the same since they are equivalent?

There's no indication that floating point number are involved (sales volumes are usually integer or even big integer) so that doesn't apply hence they will give the same values, and hence are the same.

Or have I (and many others) misunderstood?

Derek

Tim Benninghoff SSCrazy Points: 2593 More actions · Answer 14

bitbucket (9/23/2008)
I would like to thank all who have commented. I have learned a little more to apply to any additional QOD that I may submit. Please note that I relied on the
Merriam-Webster Dictionary definition for the words equivalent, and same which are:
Equivalent:
equal in force, amount, or value
Same:
equal in size, shape, value, or importance —usually used with the or a demonstrative (as that, those) in all senses
The main objective as I understand the QOD is to introduce readers to new and/or little known aspects of SQL Server it is my hope that this is what this question did.

I'm still confused. 'Equivalent' and 'same' are synonyms, making them interchangeable. So, I'm still struggling to understand what criteria is being used to disqualify the 2 queries from being the same.