What about using a nvarchar as a foreign key?
Asked Answered
A

1

7

Up until now my foreign keys have always been uniqueidentifiers or an int.

Would it impact performance negatively if I used a nvarchar field as a foreign key? Are there any other considerations I should be concerned about?

Allantois answered 26/2, 2014 at 5:21 Comment(3)
Is this relevant?Marikomaril
@AmitAgrawal nvarchar can be a foreign keyIslek
Yes, it adversely affects performance - because of the potential size of the varchar and the fact that it's variable length. Also: since you're comparing a string, you might run into problems with (a) spelling and (b) upper- and lowercase letters (if your collation is sometimes case-sensitive, other times case-insensitive). All in all - you can use a varchar as a FK - but I would recommend against it, if ever possibleAnaphora
M
6

Although you can make nvarchar as foreign key but I would not suggest you to use nvarchar as foreign key. There are many reasons out which I think the one which is very essential is when you will be doing JOINS then nvarchar will be slower as compared to int.

Would it impact performance negatively if I used a nvarchar field as a foreign key?

Yes definitely there would be performance issue as an int uses 4bytes of data and is usually quicker to JOIN than NVARCHAR.

You can also check space used by using the stored procedure sp_spaceused by taking the column as int and nvarchar and you will find the difference.

EXEC sp_spaceused 'TableName'
Mousterian answered 26/2, 2014 at 5:24 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.