Does a clustered index optimize an update process?
Posted on 2006-10-30
I'm trying to understand how SQL Server will actually access the data during an update. I've read that clustered indexes are fast for reading ranges but individual reads do not have a performance boost over a non-clustered index. In an update, does the "read head' (or whatever) move between the source and target for each record regardless of being clustered so there is no performance gain over a regular index?
Background: I'm developing a system with retiree information. I get an updated Retiree file each month from HR's mainframe with updated address etc. HR uses RetireeID as their key field which is a composite of several numbers and letters. I update my Retiree table which contains the same fields as the HR file. I'm proposing using the RetireeID as the Primary key on my table as a clustered index rather than an identity field.
Note: my system does not capture data through a GUI.