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.
对此，他发表了如下评论:值得微软称赞的是，他们从未将这种字段称为布尔值，他们只是稍微称呼它.而且它是一个真实的位 - 如果你有 8 个或一条记录中的位域较少，只需要一个字节就可以全部存储."
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.
相关文章