Hi,
I am trying to learn about indexing.
I have looked all over and cannot get a straight answerer as to what exactly are the key columns in an index. Some places elude to that they are the primary key in a table, others say it is any column in a table. This does not make sense to me.
Can someone please tell me what they are?
and then also what are Include columns.
Thank you
Very briefly:
The key column(s) are the values that are actually sorted in order. For example, if the key is on (LastName, FirstName), the names would be physically sorted in that order, so that the names could be read in that order without being sorted.
The "primary key" is a special type of key that must be unique within the table and cannot have any NULL value in any of the columns.
Included column(s) are added to a nonclustered index so that value can be read and used without having to go back to the main table. For example, if I create this index:
CREATE NONCLUSTERED INDEX Employees__IX_LastName_FirstName ON dbo.Employees ( LastName, FirstName ) INCLUDE ( date_of_birth );
Then issue this query:
SELECT LastName, FirstName, date_of_birth
FROM Employees
ORDER BY LastName, FirstName
SQL would have to read only that index, not the main table, to return results for that query, and would not have to sort the data, since it could read it name order (sorts are expensive operations, so avoiding them helps processing speed).
Thanks that really helps
Grab some popcorn and your favorite beverage and watch these three videos if you really want to understand indexes.
1 Like