CODEWITHSUNDEEP
sqlrelational-databasesqldatatypes
giordano
giordano

Reputation: 3152

sql database - numeric or string datatype

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:

2342
3252
2641
...

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.

Upvotes: 1

Views: 310

Answers (2)

Mert Akcakaya
Mert Akcakaya

Reputation: 3129

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.

Upvotes: 1

John Patrick
John Patrick

Reputation: 186

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.

Upvotes: 3

Related Questions