0

I was wondering if SQL Server behaves differently with a unique non-clustered index versus a non-unique clustered index when it comes to KEY locks.

0

2 Answers 2

5

Theoretically yes, practically no.

If SQL Server can guarantee that a field is unique, then it can build execution plans differently. Rob Farley blogged an example with AdventureWorks where the unique index isn't even shown in the execution plan, and yet its existence let SQL Server build a better plan.

If your data really does qualify for a unique index, then specifying it as unique will ensure that SQL Server always estimates 1 row to come back when you search for one value. That'll mean you're more likely to get more granular locks instead of table locks.

However, if you're worried about lock escalation, it's probably because you're not searching for equality. You're joining to other tables, or using range searches. In those cases, the uniqueness of an index isn't going to save you.

3

Generally speaking, in a SELECT operation, SQL Server will prefer a non-clustered index to a clustered index, all other things equal. The reason is that non-clustered indexes typically are "narrower", and as such generate fewer I/Os.

Locking keys or pages in a non-clustered index instead of the clustered index may reduce locking conflicts (and, down the road, deadlocks) in specific circumstances.

The downside, on the other hand, is that having to maintain both a clustered and a non-clustered index will make change operations (INSERT, UPDATE, DELETE) take more work and more time, prolonging any locks that they create, which can increase the probability of deadlocks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.