CODEWITHSUNDEEP
sqlsql-serverindexinglockingblocking
C. Poyner
C. Poyner

Reputation: 3

SQL Server clustered index on non-unique columns

In my example I have two tables that relate to each other by the primary key of the first (master) table. The second table can have multiple rows relating back to the first (master) table. In my real world example, I have tens of thousands of rows that are being selected, updated, inserted and deleted by thousands of clients simultaneously. The issue I am experience is locking because of the concurrent updates and selects.

Will making the non unique id column relating back to the primary table a clustered index help my situation at all?

Table structure:

wks_master: wks_master_id [primary key], other_columns....
wks_std_apps: wks_std_apps_id [primary key], wks_master_id, other_columns....

Queries typically looks like:

select * 
from wks_std_apps 
where wks_master_id = @wks_master_id

update wks_std_apps 
set blah... 
where wks_master_id = @wks_master_id

Upvotes: 0

Views: 612

Answers (1)

Andrew O'Brien
Andrew O'Brien

Reputation: 1813

Ideally the second table would have a unique primary key that would server as the clustered index (this can be a combination of columns). If the value is not unique, I would use a non-clustered index which should help.

Upvotes: 1

Related Questions