November 24, 2014 at 1:30 pm
What is the easiest way to remember the definitions of clustered and non clustered indexes. I'm a bit lost and confused.
Thank You!!!
November 24, 2014 at 2:06 pm
Clustered refers to the fact that the whole table is in index order. So the data is clustered according to its index.
Gerald Britton, Pluralsight courses
November 24, 2014 at 2:35 pm
Clustered index is a book. The works in the order in the book are the index. Nonclustered indexes are the index in the back of the book. Selected pieces of the book are ordered in a different way than in the book, with pointers back to their usage in the book.
November 25, 2014 at 12:12 am
This was removed by the editor as SPAM
November 25, 2014 at 4:27 am
jacksonandrew321 (11/25/2014)
Read more about clustered indexes and non clustered indexes here
Considering that the article is completely wrong about index structure, I'd recommend not reading it.
Indexes are not binary trees.
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
November 25, 2014 at 5:56 am
The clustered index is the table. That's why it's a good idea to usually put the clustered index on the most often used path to the data. This is usually, but not always, the primary key. A non-clustered index is just a mechanism for looking up the data in different ways. Both have similar structures, a balanced tree (not a binary tree, just as Gail says) and something stored at the leaf level (meaning at the end of the branches of the tree, leaves, get it).
I think of it using old school technology. We used to have these things called phone books. It listed people by name. That's the clustered index. But, you could also get a book (mainly the police and the phone company had it) that listed phones by address. That's the non-clustered index. Same data, just laid out in a way that serves different kinds of queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2014 at 6:46 am
This was removed by the editor as SPAM
November 25, 2014 at 7:01 am
jacksonandrew321 (11/25/2014)
GilaMonster (11/25/2014)
jacksonandrew321 (11/25/2014)
Read more about clustered indexes and non clustered indexes hereConsidering that the article is completely wrong about index structure, I'd recommend not reading it.
Indexes are not binary trees.
Article is not wrong the way author has explained is different from ur perspective.
Article absolutely is wrong, in a number of ways.
From the article:
When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it.
SQL Server indexes are not binary trees. They're balanced trees (b+ trees iirc). That's two different data structures with different behaviours and attributes. His diagram of an unbalanced binary tree is an incorrect depiction on an index in two ways (only two children per node and the fact that it's unbalanced). His explanation of how SQL navigates the index is based on his incorrect description of the index being a binary tree.
Further. For nonclustered indexes, he says
Here, the class column with distinct values 1,2,3..12 will store the clustered index columns value along with it. Say for example; Let us take only class value of 1. The Index goes like this:
1: 100, 104, 105
A nonclustered index does not store distinct values, it stores all values in the table (except a filtered index which is a filtered subset of the table). In his example, if the class value of 1 occurred three times with those clustered key values, the index leaf page would have three records on it:
1, 100
1, 104
1, 105
He's described both architecture and behaviour incorrectly, anyone using that article to learn about indexes is going to learn incorrectly and be confused later when SQL does not behave the way they expect.
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
November 25, 2014 at 8:02 am
jacksonandrew321 (11/25/2014)
GilaMonster (11/25/2014)
jacksonandrew321 (11/25/2014)
Read more about clustered indexes and non clustered indexes hereConsidering that the article is completely wrong about index structure, I'd recommend not reading it.
Indexes are not binary trees.
Article is not wrong the way author has explained is different from ur perspective.
Saying it's a binary tree is wrong.
It's a balanced tree. A binary tree can only have two children and that's not the structure of the tree in SQL Server. It's right here in the documentation (just do a search, it's part way down the page).
Now, it does do a binary search through the balanced tree, so maybe that lead to an accidental statement. But, in this case, the article is wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2014 at 1:18 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply