A Fine Slice Of SQL Server
In this video, I show you various techniques you can use to make window functions faster, including proper indexing and usage of batch mode. I also demonstrate the performance difference between RANGE and ROWS.
This wraps up my series on window functions, and although I pack a lot of content into the video, I highly recommend checking out the links for deeper dives into performance.
Leave a CommentIn this video, I take you through some of the limitations of window functions in SQL Server, including the lack of intervals and oddities around the APPLY operator.
Intervals would be a really powerful addition to SQL Server’s window function capabilities, but I’m not going to hold my breath for them.
Leave a CommentIn this video, I take you through a variety of use cases for window functions, showing how you can solve common (and sometimes uncommon) business problems efficiently and effectively.
This video builds off of the prior two videos. Those prior two videos showed what the different window functions are and how they work. This one focuses primarily on solving business problems in sometimes-clever ways.
Comments closedI continue a series on window functions:
In this video, I continue a dive into each category of window function, quickly reviewing the four categories of window function (plus ordered set functions). Then, I cover offset window functions, statistical window functions, and ordered set functions.
This video includes some of the window functions I use most often (LAG(), LEAD()), some of the window functions I use least often, and even a few ordered set functions to boot. Combined, it’s about 45 minutes of content between this video and the prior.
I have the first of a two-part video up:
In this video, I walk through four categories of window function, plus a somewhat-related type of function in SQL Server. From there, I demonstrate how aggregate window functions and ranking window functions work.
The second part will come out next week and will cover the other types of window function. Otherwise, this was shaping up to be a 40-minute video and that’s a bit too long.
Comments closedIn this video (part 1 of a new series), I explain what a window function is, as well as the components of window functions.
It’s taken a couple of months for me to get back on the video production wagon. This video serves mostly as the classroom primer for what will be primarily a demo-heavy series.
Comments closedIn this video, I show how we can use the APPLY operator to remove redundancy in the SELECT clause and simplify complex calculations, all with zero performance impact.
This is, as I’ve said in the past, my favorite use case for the APPLY operator. As I’ve become older and (even) more crochety, I’ve sided more and more with “easy to read” versus “runs faster” for code. And when you get “easy to read” with no impact on “runs faster,” I’m all in.
The accounting scenario I show may be a fairly extreme case, but I’d bet that queries similar to this abound in almost any company. A big part of why complex reporting queries are so complex comes from repetition of calculations.
Comments closedIn this video, I show how we can use the APPLY operator to find records associated with a “parent” table. We look at getting the latest record, as well as any arbitrary number of records. We also spend time digging into under what circumstances the APPLY operator out-performs other common options.
During the outro segment, I dig into under what circumstances APPLY is the best tool for the job, so stick around for the end.
1 CommentIn this video, I cover some news from Microsoft around the deprecation of the Azure Machine Learning SDK v1. We’ll take a look at the upgrade guide and see what it will take to perform this upgrade.
Microsoft will still support the SDK v1 until September of 2026, so we have a year to get code sorted out. The CLI v1, however, will go away sooner, so be sure you’re keeping up on that.
Comments closedIn this video, we will take a look at two KPIs for measuring avidity. We will also show off how to use ranking window functions to order groups of customers.
Click through for the video. There are far too many measures of avidity for me to do a good job explaining them all, and so many of them are closely tied to the specific nature of the business, but hopefully this at least gives you ideas of how the business side may look at user avidity or stickiness.
Comments closed