March 13, 2017 at 9:41 am
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
March 13, 2017 at 10:11 am
Your question is quite broad, and not something that can be answered in a short concise forum post. If you could ask more concise questions we can perhaps help you in increasing your understanding of indexing.
A short answer is yes, an index can be a primary key, and yes any column in a table could be indexed.
Here are two articles I would read. I would also read about indexing in SQL Server Books Online.
https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/
http://www.sqlteam.com/article/sql-server-indexes-the-basics
March 13, 2017 at 10:20 am
Once you have worked your way through the two articles recommended by Lynn, there's this one by David Durant, which goes into more detail. It's a must-read for any professional TSQL developer: http://www.sqlservercentral.com/stairway/72399/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2017 at 10:27 am
itmasterw 60042 - Monday, March 13, 2017 9:41 AMHi,
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
I'll try to be concise.
For non-clustered indexes:
Try to picture SQL Server indexes as indexes on a book. Key columns would work as each letter on an entry in a book index. The index will sort the entries alphabetically going letter by letter. Indexes in SQL Sever will be ordered column by column (most of the time in an ascending way, but can be set to be ordered in a descending way). There's nothing on the entry that will tell you more about it, just that the entry is in the book and it will have a pointer on where to find it. If there's more information such as a small definition or anything else that would allow you to use just the index instead of looking for the pages referenced in the index, that would be the included columns. Included columns will only give additional information to prevent reading the table, but won't define the order and can be used to look up their values.
For clustered indexes:
Picture a dictionary or an encyclopedia. The key columns will still represent each character on the entry, but all the information is right on that page. This is because the clustered index is actually the table in an ordered way (at least for the internals not to be relied on for consumption).
Read Lynn's links for further information.
March 13, 2017 at 10:28 am
Well for example in one of the articles that you provided here they say in the conclusion:
"A non-clustered index is comprised of the key columns plus a pointer to the actual rows." but like everywhere else , it dose not say what they are or how would identify them in a query. I am begging to think that they are just a term that put out there that doesn't really have a meaning it just sounds good.
March 13, 2017 at 10:34 am
itmasterw 60042 - Monday, March 13, 2017 10:28 AMWell for example in one of the articles that you provided here they say in the conclusion:
"A non-clustered index is comprised of the key columns plus a pointer to the actual rows." but like everywhere else , it dose not say what they are or how would identify them in a query. I am begging to think that they are just a term that put out there that doesn't really have a meaning it just sounds good.
The key columns are the columns used to create the index. This can be any column in the table that makes sense to support a query or multiple queries using the same access path. The pointer to the actual data in the table will be either a RID (if the table is a heap) or the Primary Key if the table has a clustered index defined.
March 13, 2017 at 10:45 am
Okay, this is starting to make more sense, just not sure what you mean by "Key columns would work as each letter on an entry in a book index" I will look at that other article you recommend as well.
Thanks
March 13, 2017 at 11:21 am
itmasterw 60042 - Monday, March 13, 2017 10:45 AMOkay, this is starting to make more sense, just not sure what you mean by "Key columns would work as each letter on an entry in a book index" I will look at that other article you recommend as well.
Thanks
Think of the index at the back of a book:
boolean options, 71
Bound Trees, 554
bpool columns, 37
bracketed identifiers, 214
browser service, 56
If I'm looking for information on "Bound Trees", I would use the index at the back of the book to locate the entry "Bound Trees", see what page number is listed for it (554 in this case) and go to page 554 to get the information.
I can locate "Bound Trees" in the index easily, because it's alphabetic on topic.
If that was a SQL Server index, then the index key column would be topic, containing entries such as "Bound Trees", "bpool columns", etc
My index creation script would read CREATE INDEX idx_BackOfBook ON Book (Topic)
The page number is the pointer to the actual data.
Does that help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2017 at 12:37 pm
Yes, this make it really clear for me now. I cannot believe al the web sites I went to could not do this.
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply