The SQL Server FIRST_VALUE function makes it easy to return the “first value in an ordered set of values.”
The problem is that if that first value happens to be a NULL, there is no easy, built-in way to skip it.
While a UserVoice item exists to add the ability to ignore nulls (go vote!), today, we’re going accomplish that end result with some alternative queries.
Click through for the demo, as well as a video version of the post.
So I was playing around at work today and decided for whatever reason to see how I could get the code I was writing to fire off only in certain situations.
If it’s Sunday maybe, or if this is in a particular environment, or if a record in an admin table was something specific. I’m not sure how I’ll use this but I stumbled on Labels and decided to play with them.
Ok, so how to get to know labels. Well, in order to get them to work sometimes I have to create labels that are based on some criteria.
I do try to avoid these as much as possible, but they are valid syntax and I’ve seen a couple of cases where it makes sense to use GOTO.
What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:
This shows me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.
In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.
It’s good to keep in mind the full syntax for a window function for just this reason.
The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation.
1234 SELECT d1.Name, d2.GroupNameFROM HumanResources.Department AS d1FULL OUTER JOIN HumanResources.Department AS d2ON d2.DepartmentID > d1.DepartmentID;
If you look at the descriptions of the various join operators in the Execution Plan Reference, you will see that this query poses the optimizer for what appears to be an insolvable problem: none of the join operators can be used for this query!
But it’s possible, and Hugo explains exactly what happens, as well as places where the optimizer could be better at solving the impossible (or at least marginally difficult).
In SQL Server, if you write this query:
1234 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts)SELECT *FROM AllPostsWHERE Id = 1;
SQL Server builds a query plan for the entire operation at once, and passes the WHERE clause filter into the CTE. The resulting query plan is efficient, doing just a single clustered index seek.
In Postgres, CTEs are processed separately first, and subsequent WHERE clauses aren’t applied until later. That means the above query works just fine – but performs horribly. You’ll get much better results if you include your filters inside each CTE, like this:
123 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts WHERE Id = 1)SELECT *FROM AllPosts;
That’s less than ideal.
The comments are valuable here as well.
Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:
APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.
Item number three is something I learned from an older blog post Kendra wrote and regularly use APPLY for this purpose.
Lets start off with what is a table-valued function (TVF)? A TVF is a dynamic table produced at the time of execution, depending on parameters. Like a view, a TVF creates a result set only when it’s executed, but, unlike a view, it can be parameterized.
You get two types of TVFs, an Inline Table-Valued Function (ITVFs) and Multi-statement Table-Valued Function (MTVFs). I find them easy to remember, think of the “I” in ITVF as 1 (single statement) and the “M” in MTVF as “many” (multiple statements).
As you can imagine, a TVF produces a result set that can be used as a virtual table or view. Yes, you can actually select data from a TVF, or join it with some other tables, views, or even other TVFs. In this post I will go into more detail on ITVFs.
In my experience, the best part about using inline table-valued function is the 3-9x performance improvement you get when removing them and inlining their code. It’s a great idea but the performance cost is just too high for me.
Count the number of values
The ALL argument is the default and is unnecessary (I didn’t even know it existed until I started this post). Here you are counting the number of non NULL values in FieldName. So in a column with (1, NULL, 1, 2, 3, NULL, 1) you’ll get a count of 5. You do get a nice warning (depending on your ANSI_WARNINGS setting) if there was a NULL value though.
By the way, the ALL operator isn’t useful there, but can be useful along with its counterparts SOME and ANY. I rarely keep them in my mind, so I instead tend to write EXISTS and NOT EXISTS statements which have operate on an equivalent function.
The query calculates the coefficient of variation (defined as the standard deviation divided the mean) for the following groups, in the order as they are listed in the GROUPING SETS clause:
- Country and education – expression (g.EnglishCountryRegionName, c.EnglishEducation)
- Country only – expression (g.EnglishCountryRegionName)
- Education only – expression (c.EnglishEducation)
- Over all dataset- expression ()
Note also the usage of the GROUPING() function in the query. This function tells you whether the NULL in a cell comes because there were NULLs in the source data and this means a group NULL, or there is a NULL in the cell because this is a hyper aggregate. For example, NULL in the Education column where the value of the GROUPING(Education) equals to 1 indicates that this is aggregated in such a way that education makes no sense in the context, for example aggregated over countries only, or over the whole dataset. I used ordering by NEWID() just to shuffle the results. I executed query multiple times before I got the desired order where all possibilities for the GROUPING() function output were included in the first few rows of the result set. Here is the result.
GROUPING SETS is an underappreciated bit of SQL syntax.
To stop container:
- docker stop <container ID>
To stop all running containers:
docker stop $(docker ps -a -q)
Most of the commands are straightforward but it’s nice to have a reference guide.