January 15, 2019 at 10:06 am
If I have a query on a table that has a primary key and a description column and I am grouping by the primary key column to get an aggregation, is it better to include the description column in the GROUP BY or to use MAX(DESCRIPTION) in the SELECT statement? Obviously there is only one description per key column so it seems inefficient to group by it but want to understand the difference in how the engine would process it.
Example 1:
SELECT KEYCOLUMN,
KEYDESCRIPTION,
COUNT(*) AS RECS
FROM MYTABLE
GROUP BY KEYCOLUMN, KEYDESCRIPTION
Example 2:
SELECT KEYCOLUMN,
MAX(KEYDESCRIPTION) AS KEYDESCRIPTION,
COUNT(*) AS RECS
FROM MYTABLE
GROUP BY KEYCOLUMN
Obviously there is only one description per key column so it seems inefficient to group by it but want to understand the difference in how the engine would process it.
Thanks for your help.
January 15, 2019 at 10:23 am
Why do you need either approach? What are you trying to do? If keyColumn is the PK, then any grouping on it will return 1 row and the count will always be 1. So why not just select it?
SELECT KEYCOLUMN,
KEYDESCRIPTION
FROM MYTABLE
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 15, 2019 at 1:00 pm
It's usually more efficient to use MAX(), particularly for char columns. GROUP BY for an extra char column requires sorting/hashing that is typically much more overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 15, 2019 at 2:07 pm
Thanks, Scott. That answered my question. For Mike01, I should have used a better example: SUM(charges) or something like that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply