Storage of Bit columns for null values?
The Microsoft Documentation at https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017 says:
An integer data type that can take a value of 1, 0, or NULL.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Converting to bit promotes any nonzero value to 1.
How is it possible to store 1, 0 and NULL in a single bit?
sql-server asked Nov 28 '18 at 6:29
rileymatrileymat
172110
add a comment |
The Microsoft Documentation at https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017 says:
An integer data type that can take a value of 1, 0, or NULL.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Converting to bit promotes any nonzero value to 1.
How is it possible to store 1, 0 and NULL in a single bit?
sql-server asked Nov 28 '18 at 6:29
rileymatrileymat
172110
add a comment |
The Microsoft Documentation at https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017 says:
An integer data type that can take a value of 1, 0, or NULL.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Converting to bit promotes any nonzero value to 1.
How is it possible to store 1, 0 and NULL in a single bit?
sql-server asked Nov 28 '18 at 6:29
rileymatrileymat
172110
The Microsoft Documentation at https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017 says:
An integer data type that can take a value of 1, 0, or NULL.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Converting to bit promotes any nonzero value to 1.
How is it possible to store 1, 0 and NULL in a single bit?
sql-server sql-server asked Nov 28 '18 at 6:29
rileymatrileymat
172110
asked Nov 28 '18 at 6:29
rileymatrileymat
172110
asked Nov 28 '18 at 6:29
rileymatrileymat
172110
asked Nov 28 '18 at 6:29
rileymatrileymat
172110
asked Nov 28 '18 at 6:29
rileymatrileymat
172110
172110
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Quoting a canonical answer by @MarkByers in the question How much size “Null” value takes in SQL Server regarding how SQL Server stores NULL in general:
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
So, I would expect the BIT type to behave the same as any other column, meaning that there would be a separate bit to keep track of whether the column be NULL or not NULL. Therefore, a BIT column in SQL Server actually uses two bits to keep track of the three values.
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why anot null->nullchange to a column is a metadata only update.
– Damien_The_Unbeliever
Nov 28 '18 at 6:42
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
add a comment |
There is a NULL bitmap mask in the row header that keeps track of what columns is null or not.
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53513422%2fstorage-of-bit-columns-for-null-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Quoting a canonical answer by @MarkByers in the question How much size “Null” value takes in SQL Server regarding how SQL Server stores NULL in general:
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
So, I would expect the BIT type to behave the same as any other column, meaning that there would be a separate bit to keep track of whether the column be NULL or not NULL. Therefore, a BIT column in SQL Server actually uses two bits to keep track of the three values.
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why anot null->nullchange to a column is a metadata only update.
– Damien_The_Unbeliever
Nov 28 '18 at 6:42
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
add a comment |
Quoting a canonical answer by @MarkByers in the question How much size “Null” value takes in SQL Server regarding how SQL Server stores NULL in general:
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
So, I would expect the BIT type to behave the same as any other column, meaning that there would be a separate bit to keep track of whether the column be NULL or not NULL. Therefore, a BIT column in SQL Server actually uses two bits to keep track of the three values.
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why anot null->nullchange to a column is a metadata only update.
– Damien_The_Unbeliever
Nov 28 '18 at 6:42
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
add a comment |
Quoting a canonical answer by @MarkByers in the question How much size “Null” value takes in SQL Server regarding how SQL Server stores NULL in general:
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
So, I would expect the BIT type to behave the same as any other column, meaning that there would be a separate bit to keep track of whether the column be NULL or not NULL. Therefore, a BIT column in SQL Server actually uses two bits to keep track of the three values.
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
Quoting a canonical answer by @MarkByers in the question How much size “Null” value takes in SQL Server regarding how SQL Server stores NULL in general:
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
So, I would expect the BIT type to behave the same as any other column, meaning that there would be a separate bit to keep track of whether the column be NULL or not NULL. Therefore, a BIT column in SQL Server actually uses two bits to keep track of the three values.
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
answered Nov 28 '18 at 6:38
Tim BiegeleisenTim Biegeleisen
232k1399157
232k1399157
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why anot null->nullchange to a column is a metadata only update.
– Damien_The_Unbeliever
Nov 28 '18 at 6:42
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
add a comment |
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why anot null->nullchange to a column is a metadata only update.
– Damien_The_Unbeliever
Nov 28 '18 at 6:42
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why a
not null -> null change to a column is a metadata only update.– Damien_The_Unbeliever
Nov 28 '18 at 6:42
The quote isn't quite right though. All columns have a corresponding bit in the null bitmap, not just nullable ones. That's why a
not null -> null change to a column is a metadata only update.– Damien_The_Unbeliever
Nov 28 '18 at 6:42
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
@Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green!
– Tim Biegeleisen
Nov 28 '18 at 6:43
add a comment |
There is a NULL bitmap mask in the row header that keeps track of what columns is null or not.
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
add a comment |
There is a NULL bitmap mask in the row header that keeps track of what columns is null or not.
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
add a comment |
There is a NULL bitmap mask in the row header that keeps track of what columns is null or not.
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
There is a NULL bitmap mask in the row header that keeps track of what columns is null or not.
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
answered Nov 28 '18 at 6:40
Mikael ErikssonMikael Eriksson
116k15157228
116k15157228
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53513422%2fstorage-of-bit-columns-for-null-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
