Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect data type since you would never be performing mathematical operations on them.

What is appropriate data type for these types of identifiers?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

One of the biggest advantages to using an INT over say a CHAR or VARCHAR(10) is size. The INT value will be 4 bytes and the CHAR/VARCHAR value will be 10 bytes. NCHAR/NVARCHAR would be even larger at 20 bytes. Because of this indexes on INT values are smaller and thus faster, sometimes considerably so, than a corrisponding CHAR/VARCHAR index. Also your row sizes are smaller requiring less reads per row. This is all aside from actual space storage of course. Thomas LaRock has written several blogs on "right sizing" your data types. Does this datatype make my column look fat and How to: Right sizing the datatypes currently in the buffer pool for example.

All of this of course assumes that you in fact will not be using characters in your tracking codes. If you are then of course you have no choice but to use CHAR\VARCHAR.

share|improve this answer
add comment

One of the problems with int is: they can't start with a "0". So whenever there is a risc of a starting 0 (postal zipcode, pin, ...), then don't use int.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.