I am confused about clustered index is dense or sparse. I searched for that, sources saying it is sparse, but can be dense also. What is it exactly?
Clustering index is applied when records are physically ordered on a nonkey field(called clustering field). As clustering field is nonkey so there can be more than one distinct record for a single clustering field value.
The strategy of clustering index is: There is one entry in the clustering index for each distinct value in the clustering field, containing the value, and a pointer to the first block in the data file that has a record with that value for its clustering field.
So clearly it is a sparse index.
Clustered indexing can be both dense and sparse. Lets see how
Quick recap: clustered indexing is done on non key attributes and it is sorted according to that attribute. Idea behind clustered indexing is to have one entry per unique value, as once we have pointer to that location we can find other entry for the value as it is sorted.
Case 1: non key attribute has unique value
It will be dense indexing as we have to give every value entry in index table
Case 2: non key attributes isn't unique
In this case we can have just one entry for every unique value in index table.