ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

What is the difference between simple index and cluster index in Database?

user-image
تم إضافة السؤال من قبل Kashif Akhter , VP - Wing Head Application Development , National Bank of Pakistan
تاريخ النشر: 2013/04/19
AbdelMalek AIT AMOKHTAR
من قبل AbdelMalek AIT AMOKHTAR , SharePoint Freelancer , DotNet Partners

In SQL Server, the main difference is that in the cluster index, the data(table rows) are inculded in the index (forming the leaf pages of the index tree) and are physically ordered by the index key so that can't be more than one cluster index in a table in simple (non cluster) index, only the index pages are ordered by the index key where the data are not.
so we can have multiple simple index

MOHAMED MAHMOUD EISSA
من قبل MOHAMED MAHMOUD EISSA , Software department MGR. and Senior developer , Global Media Services GMS

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

With a (Simple) non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, 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 but the drowback of clustered index is phisicaly rearanging the data takes longer time when inserting new row or updating the indexed colomn value.

to make it easyer to understand the differance consider aranging2000 numbered  books by there numbers (equal to cluster indexing) and holding a list of the numbers and book names (equal to none cluster indexing). finding book number5 is just picking the5th book and to but find book named "starting with .NET" for example you will have to open the list and see the number of the book then pick it.

then consider changing the number of book from5 to10 then you will have to go and phisicaly  move the book from position number5 to position number10 which will take more effort than just changing the number on a list.

Hope this explaines the differance clearly and why insert and update can be more heavy on clustered index while searching a clustered index is much faster.

 

 

مستخدم محذوف‎
من قبل مستخدم محذوف‎

Clustered Index - It contains Data Pages. That means the complete row information will be present in the Clustered Index Column.

Non Clustered Index - It only contains the Row Locator information in the form of Clustered Index column(if availabe) or the File Indentifier + Page Number + Total Rows in a Page. This means that the query engine must take an additional step in order to locate the actual data.

المزيد من الأسئلة المماثلة