Asking The Wrong Question
Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do.
There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find.
Now, there’s nothing in that list that says what I’m about to show you won’t work, but it’s kind of inferred here.
Statements that reference variables, such as WHERE T.col2 >= @bb.
When you’ve finished rolling your eyes around the known universe, keep reading.
This Also Goes For Assigning Variables
So, if you’re the kind of nutso-wacko that wants their variables to have values, hold onto your Librium!
Let’s check it out.
|
1 2 3 |
USE StackOverflow2010; ALTER DATABASE StackOverflow2010 SET PARAMETERIZATION FORCED; |
This sets FP on.
Now I’m gonna run two queries. One of them selects a count, and the other assigns that count to a variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId JOIN dbo.Comments AS c ON u.Id = c.UserId JOIN dbo.Badges AS b ON u.Id = b.UserId WHERE 1 = 1 AND u.Reputation > 1 AND p.Score > 0 AND c.Score > 0 AND u.Id = 8672; DECLARE @count BIGINT; SELECT @count = COUNT_BIG(*) FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId JOIN dbo.Comments AS c ON u.Id = c.UserId JOIN dbo.Badges AS b ON u.Id = b.UserId WHERE 1 = 1 AND u.Reputation > 1 AND p.Score > 0 AND c.Score > 0 AND u.Id = 8672; |
The first query is parameterized just fine.
See those little parameters? They used to be literals.
The second query doesn’t fare so well.
No. Not at all. One may even call it unfair.
Workarounds?
If you have queries that do this, and you want them to benefit from parameterization, one workaround is to insert the value you would assign your variable to into a temp table, like this.
This query will get parameterized.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT COUNT_BIG(*) AS records INTO #fp_me_plz FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId JOIN dbo.Comments AS c ON u.Id = c.UserId JOIN dbo.Badges AS b ON u.Id = b.UserId WHERE 1 = 1 AND u.Reputation > 1 AND p.Score > 0 AND c.Score > 0 AND u.Id = 8672; |
Then you can just hit that temp table for the value.
Not bad.
Not great if you do it a lot, but hey.
Thanks for reading!
