@resilient. Got this from one of the articles.
A clustered index alters the way that the rows are stored. 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. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference.
To decide which is easy to maintain, we need to consider 3 operations viz. insertion, deletion and updation.
Lets say data is like this:
-----------------------------------------------
A 1
B 3
A 2
B 4
-----------------------------------------------
These are 2 different columns.
COnsider insertion. FOr clustered index, update the index as well as sort the original table.For non-clustered index, just update the table.SO, insertion is easy with non-clustered index.
COnsider deletion, FOr clustered, check the entry in index and table as well. If any entry is deleted, we need to maintain the order in both table and index. But for unclustered index, just delete the entry from table and index and just maintain the index.
COnsider, updation. FOr clustered, we may need to sort the table and change index if any change is made to column on which indexing is done. But for non-clustered, only update the index.
So, I think non-clustered is easy to maintain.
