PK : char or varchar datatype

Last Post 26 Mar 2006 11:13 PM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
AlexB_SQL
New Member
New Member

--
22 Mar 2006 03:03 AM
Hi folks,

a developer guy asked me what is better to use on a PK, char or varchar datatype?
I asked if the length of the columns would vary a lot or would be almos fixed length. As he told me it would vary a lot, I recommended varchar.
Was I right? Varchar can improve performance by not bringing trailling spaces that would be brought when using char, but would it make any difference for PK? As PK is used as clustered index, the less its length, the less reads would be done on index, invproving performance.
I gues I thought right, but would like to know your opinion.

Alex
SQLUSA
New Member
New Member

--
26 Mar 2006 11:13 PM
CHAR is faster. If maximum length is below 30 bytes, I would use char.

varchar is space saver, it slows down things because the server cannot calculate ahead when searching. For larger than 30 bytes recommended.


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World
GoldenGal
New Member
New Member

--
05 Apr 2006 09:25 AM
If you're really interested in performance, I would suggest that you create an integer pkey for this table, give it the identity property, and use that for data manipulation internally. Then, for this char/varchar identifier, treat it like an alternate key -- maintain uniqueness by creating a unique index on the column, and use it externally, at the program level.

The "natural" key doesn't need to be the primary key of a table. The primary key should facilitate data management and database performance, not hinder it. As Kalman Toth said, char is faster (within limits) because there is no "length lookup" like with varchar data types, but long char data types can waste storage space and -- if too long, can worsen performance due to excessive I/O. Varchar is not a good candiate for primary keys. Check out InstantDoc #23450, InstantDoc #23449, and InstantDoc #5113 for more discussion on how to choose a good primary key candidate.

Good luck!
You are not authorized to post a reply.

Acceptable Use Policy