Return to Answer

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.

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.

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.

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.

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.

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.