Which out of the INTEGER and VARCHAR datatypes is better for use as primary keys and why? I am used to making my primary keys INTEGER, will using VARCAHRs have a performance penalty?
Asked
Active
Viewed 7,857 times
5
-
I do not know about performance. But `INT` column will give you simple mechanism to create a new PK, just add 1. Does VARCHAR have something like this? – Nishant Feb 09 '12 at 06:32
-
doesn't make any difference until it serves the purpose. But INT is useful when you are using auto incrementing primary keys – Sunil Kumar B M Feb 09 '12 at 06:32
-
you should use int , Index makes easy, partition make easy , easy to increment and easy to maintain uniqueness – Rashmi Kant Shrivastwa Feb 09 '12 at 06:35
-
See previous stack overflow question: http://stackoverflow.com/questions/3162202/sql-primary-key-integer-varchar – Steven Schroeder Feb 09 '12 at 06:38
-
See also http://stackoverflow.com/questions/747802/integer-vs-string-in-database – Chris Feb 09 '12 at 21:30
3 Answers
5
INT is faster for clustor index and if we want to join with other table.
You will get idea if you have understanding of Clustor Index and JOIN
Sanjay Goswami
- 1,386
- 6
- 13
3
Integers:
- Takes less room to store them
- You can set up an auto-increment to automatically get a unique id
Varchar:
- You can store non-numeric data
Speed-wise, they're almost identical.
Sam Dufel
- 17,560
- 3
- 48
- 51
1
It depends on your business need. primary key is also a column, so the type really depends on the column. For example, consider a "student" table. student_id is the primary key. If you use integer to represent the id, the primary column should be defined as INT. Or if you use something like "G12345" (G indicates graduate student), then you should use varchar.
huanyang
- 11
- 1