This appears to be caused by a combination of Simple Parameterization and Dynamic Seek.
SQL Server will, in some instances, parameterize a query that is not parameterized. But this can sometimes cause issues with implicit conversions.
What has happened here is that it has converted N'Alex'
into @1 nvarchar(4000) = 'Alex'
. Then it has transformed that WHERE DisplayName <> @1
into a Dynamic Seek. The cardinality estimation may have been inaccurate due to your original query being varchar
instead of nvarchar
.
This can have some downsides, particularly in cardinality estimation, but it does have the benefit that the server can seek both ways from a inequality predicate.
In other words,
WHERE DisplayName <> @1
becomes two seeks with the logic of:
WHERE DisplayName < @1 OR DisplayName > @1
The Sort
and Merge Interval
are not actually required here, because the two predicates must by definition be disjoint, but that is the standard Dynamic Seek setup.