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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Columns would be listed in the
inequality_columns if the query prompting the missing index request has a range predicate, such as
NOT. Microsoft Docs says this about the
inequality_columns column in
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 [<Name of Missing Index, sysname,>] 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
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:
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 [<Name of Missing Index, sysname,>] 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.