What is meaning and significance of inequality column in missing index stats?

What is meaning and significance of inequality column in missing index stats?

I'm trying to understand how does it influence the way dba will create an index?

Columns would be listed in the inequality_columns if the query prompting the missing index request has a range predicate, such as > or NOT. Microsoft Docs says this about the inequality_columns column in sys.dm_db_missing_index_details:

Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:

table.column > constant_value

Any comparison operator other than "=" expresses inequality.

Here is an example, using the StackOverflow Core Database:

USE StackOverflowCore;

SELECT TOP(100) v.PostId
    , v.BountyAmount
FROM dbo.Votes v
WHERE v.BountyAmount > 100
ORDER BY v.BountyAmount DESC;

The query plan for this query looks like:

The missing index request is:

CREATE NONCLUSTERED INDEX []
ON [dbo].[Votes] ([BountyAmount])
INCLUDE ([PostId])

Looking at the missing index DMVs, like this:

SELECT *
FROM sys.dm_db_missing_index_groups mig
CROSS APPLY sys.dm_db_missing_index_columns(mig.index_handle) mic

index_group_handle
index_handle
column_id
column_name
column_usage

2
1
4
BountyAmount
INEQUALITY

2
1
2
PostId
INCLUDE

As you can see, the BountyAmount is included in the column_usage column as an INEQUALITY column, since we're asking for values greater than a specific amount.

Adding an equality predicate to the WHERE clause, as in:

SELECT TOP(100) v.PostId
    , v.BountyAmount
FROM dbo.Votes v
WHERE v.BountyAmount > 100
    AND v.UserId = 1000  /*  THIS IS NEW */
ORDER BY v.BountyAmount DESC;

Results in the missing index query results showing the following:

index_group_handle
index_handle
column_id
column_name
column_usage

5
4
3
UserId
EQUALITY

5
4
4
BountyAmount
INEQUALITY

5
4
2
PostId
INCLUDE

As you can see, the UserId column is now showing up with an EQUALITY indicator, and the resulting missing index looks like:

CREATE NONCLUSTERED INDEX []
ON [dbo].[Votes] ([UserId],[BountyAmount])
INCLUDE ([PostId])

The takeaway here is that the INEQUALITY key columns in an index should typically be listed after the EQUALITY key columns.

StackExchange Database Administrators Q#301139, answer score: 8