SQL：Bit 或 char (1) 哪个更好
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5106166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: What is better a Bit or a char(1)
提问by renanleandrof
Is there any performancedifference on retrieving a bit or a char(1) ?
检索 bit 或 char(1)是否有任何性能差异？
Just for curiosity =]
只是出于好奇=]
UPDATE: Suposing i'm using SQL Server 2008!
更新：假设我使用的是 SQL Server 2008！
回答by marc_s
For SQL Server: up to 8 columns of type BITcan be stored inside a single byte, while each column of type CHAR(1)will take up one byte.
对于 SQL Server：BIT一个字节内最多可以存储8 个类型的列，而每个类型的列CHAR(1)将占用一个字节。
On the other hand: a BITcolumn can have two values (0 = false, 1 = true) or no value at all (NULL) - while a CHAR(1)can have any character value (much more possibilities)
另一方面：一BIT列可以有两个值（0 = false，1 = true）或根本没有值（NULL） - 而 aCHAR(1)可以有任何字符值（更多的可能性）
So really, it comes down to:
所以真的，归结为：
- do you really need a true/false (yes/no) field? If so: use
BIT - do you need something with more than just two possible values - use
CHAR(1)
- 你真的需要一个真/假（是/否）字段吗？如果是这样：使用
BIT - 你需要的东西不仅仅是两个可能的值 - 使用
CHAR(1)
I don't think it makes any significant difference, from a performance point of view - unless you have tens of thousands of columns. Then of course, using BITwhich can store up to 8 columns in a single byte would be beneficial. But again: for your "normal" database case, where you have a few, a dozen of those columns, it really doesn't make a big difference. Pick the column type that suits your needs- don't over-worry about performance.....
从性能的角度来看，我认为它没有任何显着差异 - 除非您有数万列。那么当然，使用BITwhich 可以在单个字节中存储多达 8 列将是有益的。但同样：对于您的“普通”数据库案例，您有几个，一打这样的列，这真的没有太大区别。选择适合您需求的色谱柱类型- 不要过度担心性能.....
回答by SQLMenace
a bit and a char(1) will both take a 1 byte to store,assuming you only have 1 bit column in the table, SQL Server will store up tp 8 bit columns in 1 byte. I don't think there is a difference in performance.
a bit 和 char(1) 都需要 1 个字节来存储，假设表中只有 1 个位列，SQL Server 将在 1 个字节中存储 tp 8 位列。我认为性能上没有区别。
One thing to be aware of is that you can't do sum on a bit column
需要注意的一件事是您不能对位列进行求和
CREATE TABLE #test( a BIT)
INSERT #test VALUES (1)
INSERT #test VALUES (1)
SELECT sum(a) FROM #test
Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for sum operator.
消息 8117，级别 16，状态 1，第 1 行
操作数数据类型位对于求和运算符无效。
you have to convert it first
你必须先转换它
SELECT sum(CONVERT(INT,a)) FROM #test
回答by Adam Matan
It's implementation dependent. One DBMS might have the same performance, while another might have differences.
它依赖于实现。一个 DBMS 可能具有相同的性能，而另一个可能有差异。
回答by Jon Egerton
As Adam says, it depends on the database implementing the data types properly, but in theory the following holds:
正如亚当所说，这取决于正确实现数据类型的数据库，但理论上如下：
Bit:
少量：
Will store 1 or 0 or null. Only takes a Bit to store the value (by definition!). Usually used for true or false, and many programming languages will interpret a bit as a true or false field automatically.
将存储 1 或 0 或 null。只需要一个位来存储值（根据定义！）。通常用于 true 或 false，许多编程语言会自动将 bit 解释为 true 或 false 字段。
Char[1]:
字符[1]：
A char takes 8 bits, or one byte, so its 8 times larger when stored. You can store (pretty much) any character in there. Will probably be interpreted as a string by programming languages. I think Char[1] will always take the full byte, even when empty, unless you use varchar or nvarchar.
一个字符需要 8 位或一个字节，因此存储时它会大 8 倍。您可以在其中存储（几乎）任何字符。可能会被编程语言解释为字符串。我认为 Char[1] 将始终采用完整字节，即使是空的，除非您使用 varchar 或 nvarchar。
回答by Aaron Kempf
use a bit. ALWAYS use the smallest possible datatype. it is critical once you start getting large tables.
用一点。始终使用尽可能小的数据类型。一旦您开始获得大表，这一点至关重要。
