About choosing Data type

Question

If in one of my columns in my Table I want the values as either Yes, No or Optional then what data type do I need to use?

Answer 1

BIT:

• takes 1 byte, but up to 8 BIT fields can be merged into a single BYTE in SQL Server.
• stores one of two values: 1 (meaning true) and 0 (meaning false) so the column needs to be nullable in order for NULL to pass as your third value

CHAR(1)

• takes 1 byte
• 26 characters if case insensitive ASCII vs 52 if case sensitive

TINYINT

• takes 1 byte
• values zero to 255

Performance

All of the options take the same amount of space, making performance equivalent for JOINs/etc.

Comparison

BIT is not the wisest choice if there's any chance of the possible values changing. CHAR(1) is immediately readable IE: Y, N, O. TINYINT is a good choice for the primary key in a table you want to relate via foreign key, and store the descriptive text in another column.

Conclusion:

CHAR(1) would be my choice if not using a foreign key relationship, TINYINT otherwise.
With CHAR(1), having a natural primary key that is a single character is very unlikely. Assuming a natural key based on the leading character fails if you have 2+ words that start with the same character, and causes grief if the label needs to change because the key should also change and be perpetuated (unless you're lazy & like explaining why a code doesn't follow the same scheme as the others). CHAR(1) also provides roughly a fifth of the possibilities (assuming the upper end, 52 case sensitive values) that TINYINT does -- the artificial/surrogate key insulates from description changes.

Answer 2

Use BIT for a True / False or in your case use CHAR(1) Y/N or CHAR(3) Yes / No.

Really I would use a CHAR(1) here because the extra 2 chars don't add any real value.