Why Does My Select Query Have An Assert?

Last Updated 6 years ago

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:

SELECT *
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 
(
	SELECT u.AccountId
	FROM dbo.Users AS u
	WHERE u.DisplayName = 'Eggs McLaren'
);

1

2

3

4

5

6

7

8

SELECT *

FROM dbo.Posts AS p

WHERE p.OwnerUserId =

(

SELECT u.AccountId

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!

4 Comments. Leave new

Aaron
August 8, 2018 9:06 pm

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

Reply
Erik Darling
August 9, 2018 9:35 am

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

Reply
Last week reading (2018-08-19) – SQLPlayer
August 21, 2018 2:00 am

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

Reply
Charlie
November 19, 2020 4:08 pm

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.

Reply