[Solved] Why does an inequality search on an indexed column give constant scans
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

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

Database type:

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX users_idx_displayname ON Users (DisplayName);
The optimized query:
SELECT
        Users.DisplayName,
        Users.UpVotes 
    FROM
        Users 
    WHERE
        Users.DisplayName <> N'Alex'

Related Articles



* original question posted on StackOverflow here.