And Some, Not At All
Let’s say at some point, you just didn’t know any better, and you wrote a scalar function to make some common thing you needed to do all “modular” and “portable” and stuff.
Good on you, not repeating yourself. Apparently I repeat myself for a living.
Anyway, you know what stinks? When you hit divide by zero errors. It’d be cool if math fixed that for us.
Does anyone know how I can get in touch with math?
Since you’re a top programmer, you know about this sort of stuff. So you write a bang-up function to solve the problem.
Maybe it looks something like this.
CREATE OR ALTER FUNCTION dbo.safety_dance(@n1 INT, @n2 INT) RETURNS INT WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN RETURN ( SELECT @n1 / NULLIF(@n2, 0) ); END GO
You may even be able to call it in queries about like this.
SELECT TOP (5) u.DisplayName, fudge = dbo.safety_dance(SUM(u.UpVotes), COUNT(*)) FROM dbo.Users AS u GROUP BY u.DisplayName ORDER BY fudge DESC;
The problem is that it makes this query take a long time.
At 23 seconds, this is probably unacceptable. And this is on SQL Server 2019, too. The function inlining thing doesn’t quite help us, here.
One feature restriction is this, so we uh… Yeah.
The UDF does not contain aggregate functions being passed as parameters to a scalar UDF
But we’re probably good query tuners, and we know we can write inline functions.
This is a simple enough function. Let’s get to it.
CREATE OR ALTER FUNCTION dbo.safety_dance_inline(@n1 INT, @n2 INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT @n1 / NULLIF(@n2, 0) AS safety );
Will it be faster?
SELECT TOP (5) u.DisplayName, fudge = (SELECT * FROM dbo.safety_dance_inline(SUM(u.UpVotes), COUNT(*))) FROM dbo.Users AS u GROUP BY u.DisplayName ORDER BY fudge DESC;
Well, yes. Mostly because it throws an error.
Msg 4101, Level 15, State 1, Line 35 Aggregates on the right side of an APPLY cannot reference columns from the left side.
Well that’s weird. Who even knows what that means? There’s no apply, here.
What’s your problem, SQL Server?
To get around this restriction, we need to also rewrite the query. We can either use a CTE, or a derived table.
--A CTE WITH counts AS ( SELECT u.DisplayName, SUM(Upvotes) AS Upvotes, COUNT(*) AS records FROM dbo.Users AS u GROUP BY u.DisplayName ) SELECT TOP(5) c.DisplayName, fudge = (SELECT * FROM dbo.safety_dance_inline(c.Upvotes, c.records) AS s) FROM counts AS c ORDER BY fudge DESC; --A derived table SELECT TOP(5) c.DisplayName, fudge = (SELECT * FROM dbo.safety_dance_inline(c.Upvotes, c.records) AS s) FROM ( SELECT u.DisplayName, SUM(Upvotes) AS Upvotes, COUNT(*) AS records FROM dbo.Users AS u GROUP BY u.DisplayName ) AS c ORDER BY fudge DESC;
Is it faster? Heck yeah it is.
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.