SQL Server Scalar UDF Inlining And Security Functions

You’ve Got No Security

Despite being a bouncer for many years, I have no interest at all in security.

Users, logins, roles, grant, deny. Not for me. I’ve seen those posters, and they’re terrifying.

Gimme 3000 lines of dynamic SQL any day.

This is a slightly different take on yesterday’s post, which is also a common problem I see in queries today.

Someone wrote a function to figure out if a user is trusted, or has the right permissions, and sticks it in a predicate — it could be a join or where clause.

High Finance

Stack Overflow isn’t exactly a big four accounting firm, but for some reason big four accounting firms don’t make their databases public under Creative Commons licensing.

So uh. Here we are.

And here’s our query.

There’s a function in that join to the Votes table. This is what it looks like.

Bankrupt

There’s not a lot of importance in the indexes, query plans, or reads.

What’s great about this is that you don’t need to do a lot of analysis — we can look purely at runtimes.

It also doesn’t matter if we run the query for a trusted (22656) or untrusted (2788872) user.

In compat level 140, the runtimes look like this:

In compat level 150, the runtimes look like this:

In both runs, the trusted user is first, and the untrusted user is second.

Sure, the trusted user query ran half a second longer, but that’s because it actually had to produce data in the join.

One important thing to note is that the query was able to take advantage of parallelism when it should have (CPU time is higher than elapsed time).

In older versions (or even lower compat levels), scalar valued functions would inhibit parallelism. Now they don’t when they’re inlined.

Thanks for reading!

1 thought on “SQL Server Scalar UDF Inlining And Security Functions”

Leave a Reply

Your email address will not be published. Required fields are marked *