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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » FK selection (an nvarchar(30) column or four...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Eric.Johns

Posted Thursday, June 24, 2010 8:22 AM

Forum 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

Stefan_G

Posted Thursday, June 24, 2010 8:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 2:13 AM
Points: 275, Visits: 750

It will probably not make much difference regarding performance.

I expect the four int columns to be slightly faster.

Post #942500

Bhuvnesh

Posted Friday, June 25, 2010 6:30 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #943001

weitzera

Posted Tuesday, June 29, 2010 9:57 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:59 PM
Points: 830, Visits: 526

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

Eric.Johns

Posted Tuesday, June 29, 2010 10:00 AM

Forum 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 »

Permissions