SQL Server Performance Comparison Between Over Partition By And Group By

sql-server group-by window-functions sql-server-performance
Veverke picture Veverke · Sep 8, 2015 · Viewed 7k times · Source

Although a couple of questions were already posted in SO about the difference between Over Partition By and Group By, I did not find a definitive conclusion about which performs better.

I set up a simple scenario at SqlFiddle, where Over (Partition By) seems to boast a better execution plan (I am not much familiar with them, however).

Is the amount of data in the tables supposed to change this? Does Over (Partition By) then ultimately performs better?

enter image description here

enter image description here

Answer

Adam Martin picture Adam Martin · Sep 8, 2015

The execution plan for the windowed function is clearly inferior to the execution plan for the group by (the whole group by execution plan is included in the top right of the windowed function execution plan). SQL in general is better optimized for group by than using an over clause, however each has their uses. For example, if you wanted to output a row for each entry with the summation for the group, then windowing would probably make more sense (e.g. A.id|B.b1|sum B.b1 over A.id). Since you do not, it really doesn't. You're basically using a group by, then taking a distinct again, instead of just using the distinct that the group by implies.

Related questions

Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

I would like to display the column B in my below SQL, but when I add it to the query it gives me the following error: Column T2.B' is invalid in the select list because it is not contained …

Read More
Count number of records returned by group by

How do I count the number of records returned by a group by query, For eg: select count(*) from temptable group by column_1, column_2, column_3, column_4 Gives me, 1 1 2 I need to count the above records to get 1+1+1 = 3.

Read More
GROUP BY to combine/concat a column

I have a table as follow: ID User Activity PageURL 1 Me act1 ab 2 Me act1 cd 3 You act2 xy 4 You act2 st I want to group by User and Activity such that I end up with something like: User Activity …

Read More