Jeff Moden (7/16/2010)
Nice job, Sunil. I especially liked the fact that you compared what some folks go through with multiple queries (in one form or another) and the simplicity of WITH ROLLUP.As a side bar, you shouldn't use ORDER BY with either ROLLUP or CUBE... it's one of the very few places in SQL Server where the order is pretty much guaranteed to be correct and in the order expected.
I have to contradict. There is only one way to guarantee order in SQL Server, and that is by specifying ORDER BY. Everything else depends.
And even if it were not necessary, I'd still include it. It won't hurt performance (except maybe a few microseconds parse time), and it hardens my code against future changes to the engine.