What's the best data type for a 1 character code column in a SQL table?

Question

this is not a primary key, but merely used as a marker in existing system that marks these items with a 1 letter code ie 'N' for new, 'R' for return, etc. I was going to go with a varchar(50) as the data type but wondering if there's anything wrong with say varchar(2) instead aiming for efficiency. thx!

Answer 1

I would think char(1) would be ideal

Answer 2

I would just use Char(1).

Answer 3

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

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

Answer 4

The best datatype would be char(1).

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

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

Answer 5

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

Answer 6

And, just to throw another opinion into the mix: depending on the situation, an alternative is to create a type table ("product_status" or 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')
--etc.

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

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

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

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

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

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