SQLTeam.com | Weblogs | Forums

Clustered Index on Identy column



I have table. In this table there is data with around 50 millions of records. Each WEEK (on Monday) around one millions records are deleted (based on date) and around 1.5 millions NEW data/records is added as bulk insert.

The complete deletion and insertion of records takes nearly 15 mins.

There is an identity column without any index/key.

Also there is daily updation of existing records around (200-250) based on the identity column. The updation process is done row by row (this is business logic). There is no cursor (based on while loop the some conditions are checked and row is updated). The complete updation process takes around 30-40 mins (as there is 50 millions of records). The requirement is to increase the performance of this updation (as it daily process).

If I create an primary key along with clustered index on identity column the performance of daily updation will increase.

My query is will the creation of primary key along with clustered index on indentity column will affect the performance on deletion and addition of new records which happens each WEEK (on Monday).