Geek Answers Handbook

About choosing a data type

If in one of my columns in my table I need values ​​like Yes, No, or Optional, what data type should I use?

+2
source share
6 answers

BIT:

  • accepts 1 byte, but up to 8 BIT fields can be combined into one BYTE in SQL Server.
  • stores one of two values: 1 (true) and 0 (which means false), so the column must be zero so that NULL is passed as your third value

CHAR (1)

  • accepts 1 byte
  • 26 characters if case insensitive ASCII vs 52 if case insensitive

TINYINT

  • accepts 1 byte
  • values ​​from 0 to 255

Performance

All options occupy the same space, which is equivalent to performance for JOINs / etc.

Comparison

BIT not the wisest choice if there is a chance of a change in possible values. CHAR(1) immediately reads IE: Y, N, O. TINYINT is a good choice for the primary key in the table that you want to associate with the foreign key, and save the descriptive text in another column.

Output:

CHAR(1) would be my choice if not for using the foreign key relationship, TINYINT otherwise.
When using CHAR (1), having a primary primary key that is the only character is unlikely. Assuming that a natural key based on a lead character fails if you have 2+ words starting with the same character and cause grief if the label needs to change, because the key must also change and be immortalized (if You are not lazy and do not explain why the code does not follow the same scheme as the others). CHAR (1) also provides about a fifth of the possibilities (provided that the upper end is 52 case sensitivity values) that TINYINT does - the artificial / surrogate key isolates from the description.

+9
source

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

Indeed, I would use CHAR (1) because the extra 2 characters did not add any real meaning.

+3
source

I am surprised to see so many votes for the “Beat” here. This is a bad choice.

Semantically, NULL means "unknown", so it is not a good choice as the third (known) value. If you use it this way, you may face many problems in the future. For example, aggregate functions, GROUP BY, and unions may not behave as you expect. User interfaces also cannot handle NULL as a value well (for example, MS Access has problems with zero bit fields). You also cannot maintain data integrity by specifying the NOT NULL field.

Finally, you are probably confusing any other database / application developer along the way who is used to using the value normally.

Navigate using CHAR or TinyInt.

+3
source

Both Sergey and JonVD offer good solutions, but I agree with Sergey. Zero bit offers three options. If it is not null, then you know that the user has taken this option.

0
source

I would use char (1) or INT with a check constraint. Just to minimize potential inconsistencies between the database and any level of abstraction that you use to access it. For example, JDBC does not have TINYINT.

0
source

I agree with the options for OMG Ponies, but not for its conclusion in this case.

Use a bit column! Having one bit designated for data storage gives six other Y / N / O and one Y / N storage location for free. When you use a bit data type, always specify at least the bit as non-zero, so SQL will reserve space on the data page for the values.

0
source

More articles:


All Articles