Tuesday, May 3, 2011

How to order fields on index creation (SQL Server 2005 +) ?

As this article's figure 4 says, SQL Server 2005 + can return you a list of missing indexes. It stores 2 important info about missing indexes:

[EqualityUsage],[InequalityUsage]

If I have a missing index where:

[EqualityUsage]='col1',[InequalityUsage]='col2'

Should I create an index with Indexed Key Columns:

'col1,col2'

or

'col2,col1'

?

From stackoverflow
  • if you had only one of the two to search with, which would it be? list that first

  • col1, col2

    CREATE INDEX

    To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause.

0 comments:

Post a Comment