Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FK selection (an nvarchar(30) column or four int columns) Expand / Collapse
Author
Message
Posted Thursday, June 24, 2010 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 07, 2011 6:40 AM
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?
Post #942479
Posted Thursday, June 24, 2010 8:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
It will probably not make much difference regarding performance.

I expect the four int columns to be slightly faster.

Post #942500
Posted Friday, June 25, 2010 6:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917
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
Post #943001
Posted Tuesday, June 29, 2010 9:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:14 PM
Points: 870, Visits: 581
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.'
Post #944780
Posted Tuesday, June 29, 2010 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 07, 2011 6:40 AM
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.
Post #944782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse