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'
> 'Alex' AND <= 'zzzzzzzzzzzzzz'
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