Avoid Aggregate Distinct in T-SQl (Sql Server 2008)


Here is a discussion about the hamful effect of Aggregate Distinct syntax in SQL query (SQL Server 2008).
http://blogs.msdn.com/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx
 
The proposed solution is to use two steps to get the aggregates.
1. Get a partial aggregation derived table; 2. calculate the final aggrgation from the derived table.
 

;with PartialSums as (

SELECT

COUNT(*) AS countstarpartialcount FROM myTest  GROUP BY pvalue

)

SELECT

SUM(countstarpartialcount) AS countstar,

SUM(1) AS countproductkeys FROM partialsums

 

–" … Note that in sum(1), the 1 is actually a constant number value, not a column reference."

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s