How it matters using Varchar, Integer for Primary Key?

Question

How it matters using Varchar, Integer for Primary Key?

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
May 28, 2010 at 3:32 am

#140626

Hi,
Would there be any performance impact deciding Integer or Varchar data type for a primary key?
Also, would it matter more if that column being Indexed (either Clustered or Non-Clustered)?
This is one of the dicussions I had (not right now in any tuning work though..ha ha!) and would like to get clear idea from the SSC gurus.
Thanks in advance,
Suresh
Regards,
Suresh Arumugam

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

Suresh Kumar-284278 (5/28/2010)
Hi,
Would there be any performance impact deciding Integer or Varchar data type for a primary key?
Also, would it matter more if that column being Indexed (either Clustered or Non-Clustered)?
This is one of the dicussions I had (not right now in any tuning work though..ha ha!) and would like to get clear idea from the SSC gurus.
Thanks in advance,
Suresh

Yes, there performance differences, but whether or not it's negative depends on a number of factors. First, and most important, is the width of the key. With an integer, you know that it's only 4 bytes. A varchar may only be 4, but it's probably longer. A wider key means fewer entries per page and therefore a less efficient index. Further, since we're talking the primary key of a table, all the child tables are going to have to have that value stored in them as well. Again, width matters. The wider the value, the more you have to store.

The same width issue applies on the clustered/nonclustered question. The key of the clustered index is stored with each nonclustered index, so the wider the key on the cluster, the more that has to be stored with the nonclustered index, again, making it less efficient.

BUT, these are trade-offs. If your designs are based on the concepts of natural keys (not something I agree with, but I won't argue the point), then you will probably need to use wider, string-based, data types for your PK values. Just go into it knowing what the issues are so you know where to look for performance bottlenecks.

"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

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

Thanks a lot. It's Very simple and easy to understand.

Regards,
Suresh Arumugam

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 3

Suresh Kumar-284278 (5/28/2010)
Would there be any performance impact deciding Integer or Varchar data type for a primary key?

Maybe, but that does seem like the wrong question. A varchar key achieves something quite different to an integer key because strings and integers are different sets of values that could presumably mean different things. So I'd say you should first decide what keys you want from a logical, functional perspective. Then worry about how you will make it perform efficiently. If numbers are what you need then use an integer column to store them.

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 4

Thanks David!

I have decided to have integer value for the primary key and could understand the purpose of choosing integer column.

Suresh

Regards,
Suresh Arumugam