Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FK selection (an nvarchar(30) column or four int columns)


FK selection (an nvarchar(30) column or four int columns)

Author
Message
Eric.Johns
Eric.Johns
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 37
To make a long story short, I have to add a new table with an FK to an existing table. The existing table has a PK made up of 4 int columns (the largest value in any one of these is 4 digits), there is also an nvarchar(30) column in this table that is always unique. What would be the best thing to do, query performance wise, as far as a FK in my new table, using the 4 int columns or the nvarchar(30) as an FK?
Stefan_G
Stefan_G
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 953
It will probably not make much difference regarding performance.

I expect the four int columns to be slightly faster.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
Eric.Johns (6/24/2010)
What would be the best thing to do, query performance wise, as far as a FK in my new table, using the 4 int columns or the nvarchar(30) as an FK?
i would go with stefan but still for concrete results, test it with large volume data.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
weitzera
weitzera
SSC Eights!
SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)

Group: General Forum Members
Points: 896 Visits: 629
Of course the best answer you'll get is "It Depends..." but anyway, How is the table indexed? If the default indexing (clustered on all four ints) is used, I'd expect using them as your fk would be your best bet. The reality is it's going to depend on the specifics of your data, and what your query patterns look like.



Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

Eric.Johns
Eric.Johns
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 37
Yes the default clustered index is on all 4 ints. I suspect this will be faster as stated. Thanks for the replies.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search