Saturday, 3 February 2018

sql server - What do Clustered and Non clustered index actually mean?



I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes.
I googled and what I found was :




A clustered index is a special type of index that reorders the way
records in the table are physically

stored. Therefore table can have only
one clustered index. The leaf nodes
of a clustered index contain the data
pages. A nonclustered index is a
special type of index in which the
logical order of the index does not
match the physical stored order of
the rows on disk. The leaf node of a
nonclustered index does not consist of
the data pages. Instead, the leaf

nodes contain index rows.




What I found in SO was What are the differences between a clustered and a non-clustered index?.



Can someone explain this in plain English?


Answer



With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.



With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.




It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.



Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.


No comments:

Post a Comment

casting - Why wasn't Tobey Maguire in The Amazing Spider-Man? - Movies & TV

In the Spider-Man franchise, Tobey Maguire is an outstanding performer as a Spider-Man and also reprised his role in the sequels Spider-Man...