T-SQL 2008

Question

Post reply

T-SQL 2008

Ron McCullough

SSC Guru

Points: 63877
More actions
September 30, 2009 at 10:54 pm

#210358

Comments posted to this topic are about the item T-SQL 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 45 total)

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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 1

I'm of the opinion that the answer(s) are incorrect.

CUBE and ROLLUP where already available in SQL Server 2005, so they were not introduced with SQL Server 2008.

bas de zwart Ten Centuries Points: 1117 More actions · Answer 2

Mighty (10/1/2009)
I'm of the opinion that the answer(s) are incorrect.
CUBE and ROLLUP where already available in SQL Server 2005, so they were not introduced with SQL Server 2008.

I second this opinion.

Warren Gilbert Ten Centuries Points: 1358 More actions · Answer 3

I am also of the opinion that the answer is incorrect.

The question specifically asks for extensions (subclauses) to the GROUP BY clause.

GROUPING_ID is a function, NOT a sub-clause of the GROUP BY clause, and can only be used in the SELECT, HAVING and ORDER BY clauses.

See http://msdn.microsoft.com/en-us/library/bb510624.aspx.

hodgy SSCertifiable Points: 5906 More actions · Answer 4

Warren Gilbert (10/1/2009)
I am also of the opinion that the answer is incorrect.
The question specifically asks for extensions (subclauses) to the GROUP BY clause.
GROUPING_ID is a function, NOT a sub-clause of the GROUP BY clause, and can only be used in the SELECT, HAVING and ORDER BY clauses.
See http://msdn.microsoft.com/en-us/library/bb510624.aspx.

i'm with you on this

Life: it twists and turns like a twisty turny thing

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Mighty (10/1/2009)
I'm of the opinion that the answer(s) are incorrect.
CUBE and ROLLUP where already available in SQL Server 2005, so they were not introduced with SQL Server 2008.

SQL Server 2005 and before supported WITH CUBE and WITH ROLLUP. These are now deprecated since they can be replaced by using GROUPING SETS with the new CUBE and ROLLUP functions.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Mighty SSCrazy Eights Points: 9063 More actions · Answer 6

Hugo Kornelis (10/1/2009)
Mighty (10/1/2009)
I'm of the opinion that the answer(s) are incorrect.
CUBE and ROLLUP where already available in SQL Server 2005, so they were not introduced with SQL Server 2008.
SQL Server 2005 and before supported WITH CUBE and WITH ROLLUP. These are now deprecated since they can be replaced by using GROUPING SETS with the new CUBE and ROLLUP functions.

In BOL 2005 when they describe the arguments they don't mention the WITH. But if I follow your reasoning, then the correct way of writing the answers should be CUBE() and ROLLUP(), making CUBE and ROLLUP also incorrect.

The whole point is that the answers to the question left room for interpretation.

Toreador SSChampion Points: 11365 More actions · Answer 7

The question was "SQL Server 2008 introduces several extensions (subclauses), to the GROUP BY clause"

GROUPING_ID is not part of the GROUP BY clause!

If CUBE and ROLLUP were indeed part of SQL2005 then I got it wrong anyway as I thought these were new in 2008 🙁

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 8

I almost selected all them figuring it was a trick question. I guess that what I should have done because I missed it as well. :unsure:

http://brittcluff.blogspot.com/

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

I totally agree that the answer is incorrect and grouping_id is not a sub-clause, the group by subclauses listed at the following link http://technet.microsoft.com/en-us/library/ms177673.aspx did not list Grouping_ID as a sub_clause. It is treated more like an aggregate, because the columns in the function/aggregate must appear in the group by clause with a sub-clause, see http://technet.microsoft.com/en-us/library/bb510624.aspx.

:hehe:

Rick Karpel

PostXript Right there with Babe Points: 719 More actions · Answer 10

I agree, in my opinion GROUPING_ID belongs to the SELECT clause, not the GROUP BY clause.

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 11

Warren Gilbert (10/1/2009)
I am also of the opinion that the answer is incorrect.
The question specifically asks for extensions (subclauses) to the GROUP BY clause.
GROUPING_ID is a function, NOT a sub-clause of the GROUP BY clause, and can only be used in the SELECT, HAVING and ORDER BY clauses.
See http://msdn.microsoft.com/en-us/library/bb510624.aspx.

I agree

Bhavesh
.NET and SQL Server Blog

dgabele SSCertifiable Points: 5295 More actions · Answer 12

I agree as well that GROUPING_ID is not a sub clause of GROUP BY.

Mauve SSChampion Points: 11316 More actions · Answer 13

Mighty (10/1/2009)
I'm of the opinion that the answer(s) are incorrect.
CUBE and ROLLUP where already available in SQL Server 2005, so they were not introduced with SQL Server 2008.

I'll add my vote that the answer is incorrect. What has changed in SQL Server 2008 is the syntax. Per the BOL:

The GROUP BY clause has an ISO-compliant syntax and a non-ISO-compliant syntax. Only one syntax style can be used in a single SELECT statement. Use the ISO compliant syntax for all new work. The non-ISO compliant syntax is provided for backward compatibility.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Carlo Romagnano SSC-Insane Points: 22663 More actions · Answer 14

Mighty (10/1/2009)
I'm of the opinion that the answer(s) are incorrect.
CUBE and ROLLUP where already available in SQL Server 2005, so they were not introduced with SQL Server 2008.

CUBE and ROLLUP are older. They started at least with SQL Server 2000.