SQL Server Performance Comparison Between Over Partition By And Group By

Question

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?

Answer 1

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.