Why Does My Select Query Have An Assert?

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?

Lowered Expectations

You’re probably expecting some trick here, but there isn’t one. Take this query:

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.

Passive Assertive

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!

Previous Post
A Visual Guide to Choosing an Index Type
Next Post
How to Check for Non-Existence of Rows

4 Comments. Leave new

  • Would this occur in a database where there was a unique constraint so you know there will never be another Eggs McLaren?

  • Erik Darling
    August 9, 2018 9:35 am

    Aaron — no, but Display Name is far from unique.

  • […] Why Does My Select Query Have An Assert? Usually, an assert is being happened when you modify something. […]

  • I view this as a bug (on the part of the query writer), because fundamentally, they’re asking for a single result where it’s not guaranteed to get one. This could fail at any point, just by someone entering a duplicate row.
    Either a TOP 1 or an IN/EXISTS would be correct, depending on what result is wanted.


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.