[ACCEPTED]-What's the best data type for a 1 character code column in a SQL table?-database-design

Accepted answer
Score: 12

The best datatype would be char(1).

varchar 8 means variable width, that is, if on a varchar(50) you 7 store only 3 characters, there will be no 6 47 wasted bytes. But there is a (small) performance 5 hit on accessing it as it has to go check 4 the actual length of the field for each 3 row.

If the requirement changes later you 2 can alter table and modify the datatype 1 without much trouble.

Score: 5

I would think char(1) would be ideal


Score: 3

I would just use Char(1).


Score: 1

char(1) if it's always 1 character.

Unless 5 you see good reason that it should be expanded 4 in the future - and even then, you might 3 want to look at the design and use a surrogate 2 key to a lookup table with your varchar(50) or 1 whatever.

Score: 1

I'd agree with @Cade Roux - if you consider 7 the possibility of more than 26 states of 6 record then consider converting this into 5 an FK to a state table. Also gives you 4 the opportunity convert this into a data 3 driven application e.g. by adding labels 2 to the state-table rather than hard-coding 1 them into the app.

Score: 1

And, just to throw another opinion into 20 the mix: depending on the situation, an 19 alternative is to create a type table ("product_status" or 18 something like that) of the form:

CREATE TABLE Product_Status (
    status_id INT NOT NULL PRIMARY KEY, 
    description VARCHAR(50) NOT NULL
INSERT INTO Product_Status (status_id, description) 
    VALUES (1, 'New')
INSERT INTO Product_Status (status_id, description) 
    VALUES (2, 'Return')

Then you 17 could use an int column in the original 16 table to connect to this table via a foreign 15 key.

ALTER TABLE OriginalTable 
  ADD status_id INT NOT NULL REFERENCES Product_Status(status_id)

There are advantages to either approach. A 14 char(1) is smaller, and in this situation, probably 13 more readable, but a specialized table gives 12 you a little more visibility and maybe even 11 ease of management (if you want to add and 10 remove possible values of this field later). IN 9 this case, I'd personally go with CHAR(1) as 8 people are suggesting, but if it's less 7 obvious, this is a route to consider.

In 6 any event, if you do use a CHAR(1), it's 5 a good idea to put a column constraint on 4 there to make sure that illegal values don't 3 find their way in:

ALTER TABLE OriginalTable ALTER status 
  ADD CONSTRAINT [Check_Valid_Values] 
    CHECK status in ('N', 'R' /* etc ... */)

A little tiny runtime 2 penalty now might save a huge data corruption 1 headache later.

More Related questions