0

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?

enter image description here

5

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:

enter image description here

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
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 [<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.

3

predicates

Equality columns:

  • =
  • IS NULL
  • INTERSECT
  • EXCEPT

Inequality columns:

  • >=
  • <=
  • >
  • <
  • != and <>
  • IS NOT NULL
  • BETWEEN
  • LIKE
4
  • What is the significance of equality and inequality columns with regards to creating a new index? Im trying to understand how does it influence the way dba will create the index?
    – variable
    Oct 14 at 18:28
  • EXCEPT and INTERSECT are set operators, not in the same category as =, !=, >=, etc. I fail to see how they can be categorized in this way. Unless you have examples where queries with EXCEPT/INTERSECT produce appearance in missing index stats. @Charlieface Oct 15 at 16:51
  • @ypercubeᵀᴹ Here you go: a missing index hint, which claims INTERSECT is an equality dbfiddle.uk/…. If you think about what these two operators do, even though they work on whole sets in the query, they actually function as semi- and anti-joins with all columns being equality predicates. The only difference between SELECT x FROM t1 INTERSECT SELECT y FROM t2 and SELECT x FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.x=t2.y) is how they deal with nulls Oct 16 at 18:30
  • thnx @Charlieface. I suppose one can get the same with JOINs as well. Oct 17 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.