I have columns in my application that have values that always have 4 digits, e.g. '0100' or '1230'. One of examples could be Merchant Category Code.
For the time being, I have set the type of this column to
char (4). I have many columns of this type. One of these types of columns is also included in nonclustered
UNIQUE index. I would like to establish one approach to this type of column.
Is it better to use
char (4) or maybe
smallint, padding in the application by adding
' ' or
0 to the beginning?
The table in which I have these columns is partitioned and will contain about 300 million records.
UPDATE will probably not be executed on columns with this type. They will also not appear in
WHERE (maybe sometimes in ad hoc reports).
Which data type should I choose?
It's like a zip code, it can be numeric but you must store leading zeros
Exactly. I am thinking what is better approach in terms of performance. Also have to take into consideration that if I store it as
smallint, the application code has to parse it to string and add leading zeros.