关于选择数据类型

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?

推荐答案

BIT:

• 占用 1 个字节，但在 SQL Server 中最多可以将 8 个 BIT 字段合并为一个 BYTE.
• 存储两个值之一:1(表示真)和 0(表示假)，因此该列需要可以为空，以便 NULL 作为您的第三个值传递
• 占用 1 个字节
• ASCII 不区分大小写为 26 个字符，区分大小写为 52 个字符
• 占用 1 个字节
• 值从 0 到 255

所有选项占用相同的空间量，使得 JOIN/etc 的性能相当.

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

BIT 如果可能的值有任何变化的机会，则不是最明智的选择.CHAR(1) 是立即可读的 IE: Y, N, O.TINYINT 是您想要通过外键关联的表中的主键的不错选择，并且将描述性文本存储在另一列中.

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.

CHAR(1) 将是我的选择，否则 TINYINT.
使用 CHAR(1)，自然主键是单个字符的可能性很小.如果您有 2 个以上以相同字符开头的单词，则假设基于前导字符的自然键会失败，并且如果标签需要更改，则会导致悲伤，因为键也应该更改并永久保留(除非您很懒惰& 喜欢解释为什么代码不遵循与其他代码相同的方案).CHAR(1) 还提供了 TINYINT 所做的大约五分之一的可能性(假设上限为 52 个区分大小写的值)——人工/代理键与描述更改隔离.

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.