Why use the INCLUDE clause when creating an index?

by

If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause is where you use INCLUDE.

The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree.
This makes the index smaller because it’s not part of the tree

INCLUDE columns are not key columns in the index, so they are not ordered.
This means it isn’t really useful for predicates, sorting etc as I mentioned above. However, it may be useful if you have a residual lookup in a few rows from the key column(s)

Another MSDN article with a worked example

More Related Contents:

  1. SQL Server query to find all permissions/access for all users in a database
  2. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  3. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  4. Is it possible to force row level locking in SQL Server?
  5. Regular Expressions in SQL Server servers?
  6. SQL Server GUID sort algorithm. Why?
  7. Should every User Table have a Clustered Index?
  8. What happens to the primary key Id when it goes over the limit?
  9. Drop primary key using script in SQL Server database [duplicate]
  10. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  11. SQL Server recursive query
  12. nvarchar(max) vs NText
  13. INFORMATION_SCHEMA vs sysobjects
  14. “Prevent saving changes that require the table to be re-created” negative effects
  15. What are row, page and table locks? And when they are acquired?
  16. Table Valued Function Killing My Query Performance
  17. Export from SQL Server to Excel
  18. What is the best way to create and populate a numbers table?
  19. Infinite loop CTE with OPTION (maxrecursion 0)
  20. SQL Server IN vs. EXISTS Performance
  21. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  22. PIVOT in sql 2005
  23. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  24. Concatenate values based on ID
  25. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  26. How to update primary key
  27. Should I index a bit field in SQL Server?
  28. How to change column datatype in SQL Server database without losing data?
  29. Convert UTC Milliseconds to DATETIME in SQL server
  30. How to convert text column to datetime in SQL

Leave a Comment