Difference between clustered index and non clustered index.Uses?


#1

Difference between clustered index and non clustered index.In which case we should should use which ?


#2

Clustered Index
A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

Non-Clustered Indexes
A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.

To understand how and where implement these indexes… please refer below link…


#3
                                           indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow.
                   When you create a clustered index on a column (or a number of columns), SQL server sorts the table's rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. A non-clustered index, on the other hand, does not alter the way the rows are stored in the table.
                                A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one. A Non-Clustered Index defines a logical order that does not match the physical order on disk
                        Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.so in this case we use another one.
                   but because of clustured index the deletion and insertion operations are very fast.
       when indexes size is very high then we prefer non clustered index and to avoid overhead cost we prefer non clustered index and to compress the indexes we use non clustered index

.