You can get the SQL statements defining the object from the OBJECT_DEFINITION function. I'm not sure I'd like to write a parser for it though.

An easier way might be...

Just noticed that this is the SQL Server 2008 forum, so also look at GROUPING SETS.

Books Online:

GROUPING SETS Equivalents

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

A better way to handle the NULLs generated by ROLLUP or CUBE is to use the GROUPING function.

An excellent example can be found in Books Online: Summarizing Data Using ROLLUP

Paul

And, finally, here is the (fully configurable and generally awesome) test rig code, for those that like to play along at home:

-- Switch to tempdb

USE tempdb;

-- Suppress 'x row(s) affected'...

On to Performance then.

Result: It depends :w00t:

Well it might. The original test rig had one row per section, which is probably not the most common scenario in the real...

Before moving on to performance, I'd just like to get correctness out of the way 😛

The NULLIF solution bothers me (not Peter's refined version):

DECLARE @T

TABLE (section INTEGER NOT...

I've just received permission from Itzik to share my implementation with you, so I can finally post the test rig from his Calculating Concurrent Sessions article and my CLR UDA...

It was for a bit, yes. Happy to talk about phone-related things now...

Most of the methods shown are problematic - some because they're not guaranteed to work, others because performing an aggregate over a function is always bad news for performance. ...

Isn't T-SQL the wrong tool for this sort of job?

For the curve-fitting, pass a number of built-in Point spatial data types (with geometry and geography variants) to a user-defined aggregate...

I can help with...

Hi Chris,

How about this:

DECLARE @UserRole

TABLE (

row_id INTEGER IDENTITY PRIMARY KEY,

...

Barry,

Made a few improvements and it's now 30%+ faster than T-SQL from 5,000 rows upward. Below that there's no repeatable difference between the two. I sent you the...

-- Sample table

DECLARE @Question

TABLE (

question_id INTEGER IDENTITY PRIMARY KEY,

...

RBarryYoung (2/24/2010)

---

Or are you still trying to figure out where all of the extra CPU time for the CLR Aggregate, that the QP didn't predict, is coming from? :-D:-D...