0

I am using SQL Server 2012 and for one of the table I see it has created primary key non-clustered (composite key) and clustered index on different column? Can somebody help me to understand what will happen in this situation?

  1. Does this going to degrade performance for DML operations? If yes how to measure it?
  2. Will this be causing locking/blocking/deadlocks for this table when performing DML operation during concurrency ?

Note: this table has a huge number of records in it ~10 million

Improve this question
3
  • 1
    See Myth 1 sqlconsulting.com/archives/four-indexing-myths Commented Mar 2, 2020 at 18:13
  • @MartinSmith - Thank you, this is very good information Commented Mar 2, 2020 at 18:25
  • @MartinSmith - Can we get all myths of indexes? it will be good i you have any link to explore more on it. Commented Mar 3, 2020 at 9:05

1 Answer 1

Reset to default
1

One common scenario where you might end up with a primary key which is a non clustered composite key is a junction table. A junction table mainly exists to store a relationship between two primary key values from other tables. A simple example would be storing say relationships between students and the courses they take. As such, the primary (unique) key in such a table would actually be the combination of the two foreign key columns. That being said, there can still be a clustered index on some other column. There is nothing at all out of the ordinary here, assuming such a table falls in line with your design intentions.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.