I have a table with many columns, of which the total maximum size far exceeds the 8k border. This table contains the ModuleID column, which basically tells what type of object it is (don’t worry, I didn’t design it), of which there can be 15 different values. And then it has a unique column called propertyID, which is also IDENTITY (1,1), and then SQL Server is added. A cluster index is indicated in ModuleID, this value is always known for samples, the propertyID property is used for use (the module identifier is rarely located in the area). The table contains several million rows.
As for INSERT, my questions are:
a) Does an odd clustered key increase the likelihood that SQL Server will contain page-level locks instead of KEY (row) locks?
b) Changing the cluster key to a unique identifier propertyID, which will be increased, will allow SQL Server to use exclusive KEY locks, and they will always go to the last page of the cluster index?
Will the fact that a table (in some installations) partitioned by moduleID change your answers?
source
share