[ACCEPTED]-What's the best data type for a 1 character code column in a SQL table?-database-design
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.
I would think char(1) would be ideal
I would just use Char(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.
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.
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') --etc.
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