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.