Data Grouping and Summarising Best Practices

06 Nov 2010

SQL

When you use clauses and operators to summarize data, consider the following:

• Index frequently aggregated columns to improve performance
• Avoid using aggregate functions on columns that contain NULL values
• Use the ORDER BY clause to guarantee the sort order of the result set
• Use ANSI-compliant GROUP BY syntax for summarized data
• Avoid using the COMPUTE and COMPUTE BY clause because it is not ANSI-compliant
• Use GROUPING SETS to control the layout of summarized data
• Use the RANK function to display ranking value in the result set
• Use the PIVOT function to create a crosstab result set
• Rewrite queries that use derived queries and CASE statements to create a crosstab result set by using the PIVOT clause