Enlighten me about INCLUDE on indexes

Question

Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES:

He gives some examples and etc. about INCLUDE, but I'm confused about the actual index he creates.

This is the query:

SELECT Id, DisplayName
FROM   Users 
WHERE  Reputation > 400000;

What I would do is:

CREATE INDEX ix_bla on USERS (Reputation) 
INCLUDE (Id, DisplayName)

I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.

But Erik did this:

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )
 INCLUDE ( Reputation )
 WHERE Reputation > 400000
 WITH ( DROP_EXISTING = ON );

The problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?

And just for a simple test, running this query for a test in a table with millions of rows:

select COL1
      ,COL2
      ,COL3
from MyTable
    where COL2 > 4513516

SQL tells me to create this index:

CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
    (
        [COL2]
    )
INCLUDE 
    (
        [COL1],
        [COL3]
    )
GO

Answer 1

what you read and did is correct ?

Suppose, you have no index on user table,then

CREATE INDEX ix_bla on USERS (Reputation) 
INCLUDE (Id, DisplayName)
WHERE Reputation > 400000;

This index is ok.

But this was not the point @Erik wanted to demonstrate.

According to that blog,suppose you already have any such index like,

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )

And you are require to create filtered index on Reputation then just be mere mentioning it in Include will do the job and save from creating expensive index.

so he dis this,

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )
 INCLUDE ( Reputation )
 WHERE Reputation > 400000
 WITH ( DROP_EXISTING = ON );

Filtered Indexes: Just Add Includes This is the new point about Filtered Index that he wanted to show.

Answer 2

Erik's intent wasn't to show that the index leading with DisplayName was a GOOD index for that query.

His point was just to show that it WOULD use that index. It's doing a scan rather than a seek, which isn't great in this scenario.

If you really wanted to create a perfect index for the query in the blog post, you'd create it on Reputation first as the key (since the WHERE clause is on Reputation.)