sql database - numeric or string datatype

Question

I have a principle question regarding definition of a code in a relational database. Let's assume (case 1) we have a code, for example, nationality code:

1
2
...
23
...
125
...

What is the preferred datatype to use for this kind of code (for example INT(3) or VARCHAR(3)?). The range is between 1 and 999.

Now, let's assume we have again a numeric code (case 2) but with fixed length, for example, 4:

What would be in this case the appropriate datatype?

In both cases there is no information, that is, the position of a digit has no meaning, the code is just a distinction id.

I would appreciate any comments. Thanks.

The answer might depend on whether it was actually a nationality code or something else, as there are standard abbreviations used in internationalization, e.g. "en-us". But typically you'd use some form of integer. Int16, int32, smallint. Depends on what is available in the RDBMS. — Tim
– Tim, Commented Mar 6, 2013 at 11:00

John Patrick · Accepted Answer · 2013-03-06 11:00:32Z

3

Integers are usually faster to search on than varchars and take up a little less space, so I'd say if there are no other factors involved, go for integer.

answered Mar 6, 2013 at 11:00

John Patrick

1861 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

giordano Over a year ago

Thanks a lot. Performance is a good reason to choose integer.

Joachim Isaksson Over a year ago

@giordano Of course, performance gains/losses would depend on the RDBMS and the INT type you choose, so test for yourself to be sure.

Mert Akcakaya · Accepted Answer · 2013-03-06 11:18:34Z

1

Strings are larger, slower in database. If you don't need something like "0289", then go with integer.

If performance is very important for you, then you can event store it as integer "289" in database and in your application you can complete it to "0289" programmatically, but it really depends.

String indexes in database is not the preferred way btw.

answered Mar 6, 2013 at 11:18

Mert Akcakaya

3,1792 gold badges33 silver badges43 bronze badges

1 Comment

giordano Over a year ago

Thanks! I will use integer. Your statement regarding index arise a question: index makes no sense for categorical variable, that is, it has to be convert/mapped to an integer and than indexed the new variable? Or do indexed categorical variables perform better than non indexed but it is not optimal and creates problems in very huge and busy database?

Collectives™ on Stack Overflow

sql database - numeric or string datatype

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related