I have a primary key on a column that is a unique identifier (width 32 char) and it is nonclustered. There are a lot of inserts, updates and selects that run on this table which is actually taking long times to complete. Sometimes the inserts and selects take almost a minute and sometimes less than 15seconds. I was wondering if i create a new identity column and make it a clustered primary key would reduce the time take it takes to complete the inserts, updates and deletes. Please let me know if this is the right approach.
Thanks a bunch.
An identity will definitely insert faster than a uniqueidentifier. As a general rule, if an identity can do what you need, choose it over a uniqueidentifier, esp. for indexing.
For the SELECTs and UPDATEs, it depends on the WHERE clauses. In general, you need to make sure you've selected the best clustering
column. There's far too little info here to make that determination.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.