Primary key position

Question

Primary key position

flavio_elorza

SSC Enthusiast

Points: 124
More actions
July 11, 2014 at 8:02 am

#287806

Hi,
I have a database with primary key positioned in the first columns of the tables. Example:
code int (pk)
name varchar (80)
int age
I need to include a new key for data organization. Recreate the primary key with placements out of order cause problems? Example below:
code int (pk)
name varchar (80)
int age
company int (pk)
Or is it better option to recreate the table as in the example below:
company int (pk)
int code (PK)
name varchar (80)
int age
Many thanks for the suggestions

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Position of the columns within the table has no effect other than on queries doing a SELECT *

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399340 More actions · Answer 2

If you were down to trying squeeze the last microsecond of performance by increasing the efficiency of performance, yes, this would matter. You would want the fixed length columns stored first and then any variable length columns. This would help get an extra row or three on a given page and could gain you maybe a few milliseconds a day, depending on the frequency of calls to the system

In short, don't bother. It really doesn't matter. The clustered key will be stored and sorted in the order you provide, regardless of the order of the columns on the table definition. That's all that really matters.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Grant Fritchey (7/11/2014)
You would want the fixed length columns stored first and then any variable length columns.

Not really, because SQL stores the columns on the page that way no matter what their order is in the metadata.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399340 More actions · Answer 4

GilaMonster (7/11/2014)
Grant Fritchey (7/11/2014)
You would want the fixed length columns stored first and then any variable length columns.
Not really, because SQL stores the columns on the page that way no matter what their order is in the metadata.

Oop, is old knowledge leaking out again? Good to know.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

flavio_elorza SSC Enthusiast Points: 124 More actions · Answer 5

flavio_elorza

SSC Enthusiast

Points: 124

July 11, 2014 at 9:48 am

#1728802

I suspected my question, thank you