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
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.