CODEWITHSUNDEEP
sqlsql-serverprimary-keyclustered-index
Owen
Owen

Reputation: 4397

Table with Int Clustered Identity & NVarChar Primary Key - Which one do I join to?

USER

UserId int - clustered index & identity
UserName nvarchar - PK
Name nvarchar
Location nvarchar

USER TYPE JOIN

UserName nvarchar - PK
UserTypeId int - PK

USER TYPE

UserTypeId int - PK
Name nvarchar

My User table didn't have the UserId and I was using the UserName as the primary key as well as the identity and clustered index column, but that was causing me fragmentation issues so I added UserId and set it to the clustered index identity.

Do I need to change my join table UserName column to the UserId, as it's the table identity and it might improve performance?

Do I leave it on the UserName because it's the primary key?

Upvotes: -1

Views: 495

Answers (2)

nvogel
nvogel

Reputation: 25526

Joining on UserId may well make some queries more efficient but be aware that there could also be costs associated with referencing UserId instead of UserName. You may end up needing to do joins where you didn't have to before because the UserName now resides only in the User table. Also you'll probably need additional application and data tier code to handle the mapping and translation between UserNames and UserIds - assuming the UserName is still what the users care about and what your business logic is dependent on. I suggest you evaluate the overall impact before deciding whether to make the change.

Many people invariably put the PRIMARY KEY constraint on whatever key columns are being referenced by foreign keys in other tables; other people prefer that a "natural key" is always designated PRIMARY KEY. It mostly comes down to convention and aesthetics and doesn't necessarily make any practical difference either way. I've certainly not noticed that query performance in SQL Server is ever affected by using a PRIMARY KEY constraint instead of a UNIQUE constraint.

Upvotes: 1

Jeremy Hutchinson
Jeremy Hutchinson

Reputation: 2045

Using an Int as the primary key and doing all joins based on that columns will be faster than using the UserName especially since you have a clustered index on that column.

You probably should change all of your joins to use the UserId and make that column the primary key. You can put a Unique Constraint on the UserName field to ensure that column remains Unique.

Upvotes: 1

Related Questions