Window Functions vs Group By

One option for improving query performance is to rewrite the query in order to obtain a completely different execution plan. In this episode we look at how it's possible to rewrite queries with Window Functions into more traditional GROUP BY syntax and what causes the difference in performance.
Subscribe and turn on notifications to never miss a weekly video: th-cam.com/users/DataWithBer...
Related blog post with example code:
bertwagner.com/2019/04/16/win...
More examples with Window Functions: • GAPS and ISLANDS in SQ...
Download the StackOverflow database for yourself: www.brentozar.com/archive/201...
Follow me on Twitter:
/ bertwagner

[@CollinReinking]

Commenting after the first minute because, regardless of how the rest of the video goes, I appreciate that someone is focusing on solving problems when your only lever to pull is your query.

[@btulkas8199]

This is great, I often find myself trying both windowed functions and group by, but never really understanding the differences they can potentially have in the optimizer.

[@ybumbu]

For problems like you described, instead of GROUP BY I tend to use ROW_NUMBER() in a CTE, partitioning by the what I want to group, and to get top values just filter the values which returns ROW_NUMBER() ... == 1. Almost every time I used this way of writing queries in SQL Server, it was at least faster and shorter than GROUP BY. Here the way I would had wrote the query:
;WITH b AS (
SELECT
Name,
UserId,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date,UserId) AS rn
FROM
dbo.Badges
)
SELECT
b.Name,
b.UserId
FROM b
WHERE rn = 1
-- to get only the first rows from the partitioning
ORDER BY
Name,UserId

[@nofatchicks6]

As a full stack dev (i.e. master of nothing) I find these types of videos really useful. I can understand the SQL and what it is supposed to do, but not always why or how.

[@jerichaux9219]

Honestly these are some of the best videos I’ve found about SQL. I love them - they’re short enough that I don’t feel like they’re a documentary, Bret’s knowledge is SUPERB, and I’ve learned quite a lot from watching it!
Love ‘em!

[@mooremike44]

Thanks for putting out these videos. Very informative, I would not have guessed that the optimizer would come up with such different plans.

[@danielheydt9864]

Another great video. Thank you! Would you consider doing a video (or videos) diving into the details of writing the queries? I know very little about Windows Functions and have always struggled with this type of query using Group Bys. I never seem to get it just right.

[@SelmanAy]

keep going mate, best sql youtube channel

[@AdamMarcus930]

Wow a good video on SQL..never thought Id see the day.

[@lbb2rfarangkiinok]

Wow, this was eye opening. Had never seen an execution plan before.

[@Zanoni7603]

"Black Magic" I say!! I'm downloading the DB now so I can try this out. Thanks for an awesome video. Love these optimization vids. Funny how few developers out there do the basics; change RBAR to set based statements, read the execution plans, and just try to reduce the number of rows. Instead, they just climb in and start slashing at code and configuring SQL and Hardware... It keeps us consultants in business :)
If I may, can I suggest a video on some useful DMVs? I love the "missing index","index usage" and "exec query stats" ones, but there may be more really good ones out there that we can add to our toolkits - there are just so many :)

[@user-ie7jx4uw1l]

Great content, man. You’ve got really kind eyes
Btw, are we sure optimizer did not cached previous result in a way so it reduces time spent?

[@alirezaghanbarzadeh1679]

Great Video! enjoyed it a lot, much beneficial. how did u learn to optimize SQL ?

[@Strickjr]

Do you have an example of how to use a group by instead of a window function with unbounded range preceding? It seems like you have to use the window function in that case, right?

[@sanilkhurana3991]

Hey Bert, great video. Quick question though, is tempdb written to disk or would it be stored only in RAM, and is that dependent on the database/configuration? Is there any reason why we can't store it in RAM(if it isn't already)?

[@SelmanAy]

best sql youtube channel

[@carolinnerabbi965]

Very useful, thanks!

[@AvengerSubzero]

Amazing job!!!!

[@beyondlife123]

Quick question
Does something similar happens with PostgreSQL?

[@maryam4071]

Is this the same for MySQL?

[@kopytko998]

Really nice video

[@yoggita]

Thanks a ton!!!!

[@peporila]

hey, u're great.

[@stefanotorelli3688]

Bert, I know... but it is very easy to say what is wrong and what is right in such kind of situations... ;)

[@SpiritOfIndiaaa]

How to get stackoverflow questions alerts to answer a specific badge ?