Why does an inequality search on an indexed column give constant scans

Question

Using the StackOverflow2010 database, I can create an index on the users table as follows:

CREATE INDEX IX_DisplayName ON dbo.Users
(
    DisplayName,
    UpVotes
)

And then run an inequality search on the key of the index:

SELECT  DisplayName,
        UpVotes
FROM    Users
WHERE   DisplayName <> N'Alex'

I get the plan here

I am trying to work out how SQL Server has gone about getting the results for this query.

The plan begins with some constant scans but the Output list is blank so it isn't clear to me what they are for.

Each constant scan then goes into a compute scalar, each of which output

Compute Scalar Node6
Expr1002 = 10
Expr1003 = NULL
Expr1004 = N'Alex'

Compute Scalar Node9 
Expr1005 = 6 
Expr1006 = N'Alex' 
Expr1007 = NULL

the concatenate operator then appears to concatenate some of the outputs above:

Expr1010 = Expr1008,Expr1006
Expr1011 = Expr1004,Expr1009
Expr1012 = Expr1002,Expr1005

But it has inputs I can't see anywhere in the plan (Expr 1008 and Expr1009)

I am also not sure why the TOP N sort is required

The Index seek makes sense - it is looking for > Expr1011 and < Expr1012. I would assume that this is basically something like

>= 'a' AND < 'Alex' 

or

> 'Alex' AND <= 'zzzzzzzzzzzzzz'

or similar.

Can someone explain to me step by step, how this plan is working and how I can understand the values of Expr1011 and Expr1012 (used in the index seek) which are produced by the concatenation operator

Answer 1

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.