Getting Sneaky With Forced Parameterization

Silly Rules

I’ve blogged about some of the silly rules about where Forced Parameterization doesn’t work.

One rule that really irked me is this one:

The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT…INTO, or FOR XML clauses of a query.

TOP and FOR XML, get used, like, everywhere.

TOP is pretty obvious in its usage. FOR XML less so, but since it took Microsoft a lifetime to give us STRING_AGG, lot of people have needed to lean on it to generate a variety of concatenated results.

Heck, I use it all over the place to put things together for in the First Responder Kit.

Examples

In a database with Forced Parameterization enabled, these queries cannot be parameterized.

If we look at the query plans for them, we can see partial parameterization:

Halfsies

The literals passed into our query outside of the illegal constructs are parameterized, but the ones inside them aren’t.

Which means, of course, that we could still end up with the very plan cache pollution that we’re trying to avoid.

For shame.

The Adventures Of Irked Erik

I figured I’d try out some different ways to get around those rules, and it turns out that APPLY is just the trick we need.

For some reason, that’s not mentioned on that page.

I can’t find a newer version.

Maybe my internet is broken?

But anyway, if we change our queries to use APPLY instead, we get full parameterization:

If we look at the new query plans, we can see that:

I see variables.

Bonkers

Partial disclaimer: I only tested this on SQL Server 2017 so far, and that’s probably where I’ll stop. Forced Parameterization is a fairly niche setting, and even if you have it turned on, you may not be able to change the queries.

It’s just something I thought was cute.

Thanks for reading!

Previous Post
Not So Forced Parameterization
Next Post
How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}