The content is just an excerpt from the complete note for SS2 Third Term Data Processing Lesson Note – Indexes. Check below to download the complete DOCUMENT
WEEK 8
TOPIC: INDEXES
CONTENT:
- Clustered vs Non-clustered Indexes
- Dense vs Sparse
SUBTOPIC 1:
What is an Index?
Is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. An index in a database works the same way in that a query is pointed to the exact physical location of data in a table.
Types of Index
Clustered vs Non-clustered Indexes
Clustered: is where the table data is physically stored in the order of the keys on the index.
- For all pages-locked tables, rows are stored in key order on pages, and pages are linked in key order.
- For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key ordering is not maintained.
Non-clustered: is where the storage order of data in the table is not related to index keys.
You can create only one clustered index on a table because there is only one possible physical ordering of the data rows. You can create up to 249 non-clustered indexes per table.
Clustered and non-clustered tree indexes are illustrated below
Thus, clustered indexes are relatively expensive to maintain when the file is updated. Another reason clustered index is expensive to maintain is that data entries may have to be moved across pages, and if records are identified by a combination of page id and slot, as is often the case, all places in the database that point to a moved record (typically, entries in other indexes for the same collection of records) must also be updated to point to the new location; these additional updates can be very time consuming.
To gain full access to the note: DOWNLOAD FILE
