Microsoft 可以在一位中存储三值字段吗?
我对 SQL/数据库完全一无所知，但我正在和一位从事大量数据库工作的朋友聊天，了解一些数据库如何使用布尔值"字段，除了 true 和 false 外，该字段还可以取值为 NULL.
I'm completely ignorant of SQL/databases, but I was chatting with a friend who does a lot of database work about how some databases use a "boolean" field that can take a value of NULL in addition to true and false.
关于这一点，他发表了以下评论:感谢微软，他们从来没有将这种字段称为布尔值，他们只是称之为一点.这是一个真实的一点 - 如果你有八个或一条记录中的位域更少，只需要一个字节就可以把它们全部存储起来."
Regarding this, he made a comment along these lines: "To Microsoft's credit, they have never referred to that kind of field as a boolean, they just call it a bit. And it's a true bit - if you have eight or fewer bit fields in a record, it only requires one byte to store them all."
这对我来说自然是不可能的 - 如果该字段可以包含三个值，您就不会将其中的八个放入一个字节中.我的朋友同意这看起来很奇怪，但恳求对底层内部结构一无所知，并说据他所知，从 SQL 端来看，这样的字段可以容纳三个值，并且确实需要一个字节的存储空间.我想我们中的一个人有电线交叉.谁能解释一下这里到底发生了什么?
Naturally that seems impossible to me - if the field can hold three values you're not going to fit eight of them into a byte. My friend agreed that it seemed odd, but begged ignorance of the low-level internals and said that so far as he knew, such fields can hold three values when viewed from the SQL side, and it does work out to require a byte of storage. I imagine one of us has a wire crossed. Can anyone explain what's really going on here?
推荐答案
我推荐阅读这篇文章以获得对空存储的一个很好的解释:SQL Server 如何真正存储 NULL-s.简而言之，空/非空位存储在不同的地方，即行的空位图.
I recommend reading this for a good explanation of null storage: How does SQL Server really store NULL-s. In short, the null/not null bit is stored in a different place, the null bitmap for the row.
来自文章:
对于允许空值的列，每一行都有一个空位图.如果该列中的行为空，则位图中的位为 1，否则为 0.
Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.
因此，虽然 8 位列的实际 值 存储在 1 个字节中，但行的空位图中有额外的位指示该列是否为 NULL...所以取决于如何你在数为了完全准确，8 位列使用2 个字节，只是分成 2 个不同的位置.
So while the actual values for 8 bit columns are stored in 1 byte, there are extra bits in the row's null bitmap that indicate if that column is NULL or not...so depends on how you're counting. To be completely accurate, 8 bit columns use 2 bytes, just split up in 2 different locations.
相关文章