You And Ert
This is a quick post because it came up with a client. I like having stuff to point people to — that’s sort of like automation, right?
Anyway! Lots of plans have Assert operators in them. But they’re usually performing modifications.
Assert operators are used to check Foreign Key and Check Constraint integrity when you modify tables. When you see them in DUI plans, you don’t bat an eye.
But what about in a select query?
You’re probably expecting some trick here, but there isn’t one. Take this query:
FROM dbo.Posts AS p
WHERE p.OwnerUserId =
FROM dbo.Users AS u
WHERE u.DisplayName = 'Eggs McLaren'
Because it’s a scalar subquery — if AccountId returns more than one value, an error is thrown — the optimizer has to check that a single value is returned somehow.
That somehow is an assert operator.
Once the Users table has been scanned and values aggregated via a Stream Aggregate, the Assert operator kicks in to validate that only one value is returned.
Is This Bad?
No, not at all. It’s just an explanation. If performance is a concern, you can try to replace the subquery with EXISTS or CROSS APPLY, but without indexes on columns being matched on, you’re not likely to see much for gains.
Like most other performance problems in SQL Server, queries and indexes tend to work together to solve them.
Thanks for reading!