CODEWITHSUNDEEP
c#sqldatabaseinformix
Anyname Donotcare
Anyname Donotcare

Reputation: 11423

Which data type should I use to handle nine-digit account numbers and why?

Which data type should I use to handle 9-digit account numbers and why?

varchar(9) or int or decimal or something else ?

I'm talking from a database perspective — and the DBMS is Informix.

Upvotes: 4

Views: 9260

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

TL;DR Use CHAR(9).

You have a number of options, most of them mentioned in the comments. The options have different trade-offs. They include:

  • CHAR(9). This uses 9 bytes of storage, but can store leading zeros and that can save on formatting in the applications. You can write a check constraint that ensures that the value always contains 9 digits. If you later need to use longer numbers, you can extend the type easily to CHAR(13) or CHAR(16) or whatever.
  • INTEGER. This uses 4 bytes of storage. If you need leading zeros, you will have to format them yourself. If you later need more digits, you will need to change the type to BIGINT.
  • SERIAL. This could be used on one table and would automatically generate new values when you insert a zero into the column. Cross-referencing tables would use the INTEGER type.
  • DECIMAL(9,0). This uses 5 bytes of storage, and does not store leading zeros so you will have to format them yourself. If you later need more digits, you can change the type to DECIMAL(13,0) or DECIMAL(16,0) or whatever.
  • BIGINT and BIGSERIAL. These are 8-byte integers that can take you to 16 digits without problem. You have to provide leading zeros yourself.
  • INT8 and SERIAL8 — do not use these types.
  • VARCHAR(9). Not really appropriate since the length is not variable. It would require 10 bytes on disk where 9 is sufficient.
  • LVARCHAR(9). This is even less appropriate than VARCHAR(9).
  • NCHAR(9). This could be used as essentially equivalent to CHAR(9), but if you're only going to store digits, you may as well use CHAR(9).
  • NVARCHAR(9). Not appropriate for the same reasons that VARCHAR(9) and NCHAR(9) are not appropriate.
  • MONEY(9,0). Basically equivalent to DECIMAL(9,0) but might attract currency symbols — it would be better to use DECIMAL(9,0).

Any other type is rather quickly inappropriate, unless you design an extended type that uses INTEGER for storage but provides a conversion function to CHAR(9) that adds the leading zeros.

Upvotes: 5

Related Questions