{"id":875,"date":"2009-05-04T14:47:00","date_gmt":"2009-05-04T14:47:00","guid":{"rendered":"\/blogs\/paul\/post\/Misconceptions-around-null-bitmap-size.aspx"},"modified":"2013-01-01T19:43:42","modified_gmt":"2013-01-02T03:43:42","slug":"misconceptions-around-null-bitmap-size","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/","title":{"rendered":"Misconceptions around null bitmap size"},"content":{"rendered":"

\nI'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (<\/font>Misconceptions around TF 1118<\/font><\/a>) but just went back to re-tag it. <\/font>\n<\/p>\n

\nIn this post I want to debunk the common myth that the null bitmap only contains bits for nullable columns. It doesn't – it has one bit per column in the table definition, as long as at least one column in the table is nullable. The 'unused' bits are always set to 1, which means 'null'. <\/font>\n<\/p>\n

\nAnd now the proof. I'm going to create two tables, with 10 columns each (meaning that the null bitmap has to have two bytes to store all the bits, plus two bytes for the count of columns in the record). The first table will have all nullable columns. The second will have the first 9 columns not nullable, and the tenth column nullable. <\/font>\n<\/p>\n

\n

\n\tCREATE TABLE t1 (
\n\t    c1 INT, c2 INT, c3 INT, c4 INT, c5 INT,
\n\t    c6 INT, c7 INT, c8 INT, c9 INT, c10 INT);
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE TABLE t2 (
\n\t    c1 INT NOT NULL, c2 INT NOT NULL, c3 INT NOT NULL,
\n\t    c4 INT NOT NULL, c5 INT NOT NULL, c6 INT NOT NULL,
\n\t    c7 INT NOT NULL, c8 INT NOT NULL, c9 INT NOT NULL,
\n\t    c10 INT);
\n\tGO<\/font>\n\t<\/p>\n

\n\tINSERT INTO t1 VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
\n\tGO<\/font>\n\t<\/p>\n

\n\tINSERT INTO t2 VALUES ( 1, 2, 3, 4, 5, 6, 7, 8, 9, NULL);
\n\tGO<\/font><\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd now let's look at the pages themselves. I'll get the pages involved using my sp_AllocationMetadata<\/font> script (see <\/font>Inside The Storage Engine: sp_AllocationMetadata – putting undocumented system catalog views to work<\/font><\/a>): <\/font>\n<\/p>\n

\n

\n\tEXEC sp_AllocationMetadata 't1';
\n\tEXEC sp_allocationMetadata 't2';
\n\tGO<\/font>\n\t<\/p>\n

\n\tObject Name  Index ID  Alloc Unit ID      Alloc Unit Type  First Page  Root Page  First IAM Page
\n\t———— ——— —————— —————- ———– ———- —————
\n\tt1           0         72057594042318848  IN_ROW_DATA      (1:152)     (0:0)      (1:153)<\/font>\n\t<\/p>\n

\n\tObject Name  Index ID  Alloc Unit ID      Alloc Unit Type  First Page  Root Page  First IAM Page
\n\t———— ——— —————— —————- ———– ———- —————
\n\tt2           0          2057594042384384  IN_ROW_DATA      (1:154)     (0:0)      (1:155)<\/font><\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd now dump them with DBCC PAGE to look at the row structure: <\/font>\n<\/p>\n

\n

\n\tDBCC TRACEON (3604);
\n\tGO
\n\tDBCC PAGE ('NullTest', 1, 152, 3);
\n\tDBCC PAGE ('NullTest', 1, 154, 3);
\n\tGO<\/font>\n\t<\/p>\n

\n\t— Record dump from t1, all ten columns nullable and not NULL <\/font>\n\t<\/p>\n

\n\tRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
\n\tMemory Dump @0x684EC060<\/font>\n\t<\/p>\n

\n\t00000000:   10002c00 01000000 02000000 03000000 †..,………….
\n\t00000010:   04000000 05000000 06000000 07000000 †…………….
\n\t00000020:   08000000 09000000 0a000000 0a0000fc<\/u><\/strong> †…………….<\/font>\n\t<\/p>\n

\n\t— Record dump from t2, first nine columns not nullable, tenth column nullable and NULL <\/font>\n\t<\/p>\n

\n\tRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
\n\tMemory Dump @0x684EC060<\/font>\n\t<\/p>\n

\n\t00000000:   10002c00 01000000 02000000 03000000 †..,………….
\n\t00000010:   04000000 05000000 06000000 07000000 †…………….
\n\t00000020:   08000000 09000000 21212121 0a0000fe<\/u><\/strong> †……..!!!!….<\/font>\n\t<\/p>\n<\/blockquote>\n

\nIf you try this yourself, you may get a different bit pattern for the NULL c10 (I got 0x21212121<\/font>). It's a bit of a crap-shoot depending on what memory SQL Server reuses to create the record in memory before writing to the page – the 4 bytes of the NULL column aren't overwritten from the previous usage of the memory (and don't need to be). <\/font>\n<\/p>\n

\nIn the first record, you can see that the null bitmap (underlined and in bold) says there's 10 columns (the 0x0a00<\/font> gets byte-reversed into 0x000a<\/font>) and the null bitmap is 0xfc00<\/font>, which is 1111110000000000<\/font> – 6 unused bits all set to 1 and 10 not-NULL column values.<\/font>\n<\/p>\n

\nIn the first record, you can see that the null bitmap (underlined and in bold) says there's 10 columns again and the null bitmap this time is 0xfe00<\/font>, which is 1111111000000000<\/font> – 6 unused bits all set to 1, 1 column NULL and 9 columns not-NULL. Column 1 is the far right bit, so column 10 is the tenth from right (the first '1').<\/font>\n<\/p>\n

\nThis clearly shows that all columns are represented in the null bitmap even if only one of the columns is nullable. It's the same if on the first column in the table is nullable, I'll leave that for you to prove to yourself.<\/font>\n<\/p>\n

\nNext up: the next misconception I come across!<\/font>\n<\/p>\n

\n[Edit 08\/02\/11: In 2008 if all columns are defined as SPARSE, there will not be a null bitmap at all – very special case!]<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,61,62],"tags":[],"class_list":["post-875","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-misconceptions","category-on-disk-structures"],"yoast_head":"\nMisconceptions around null bitmap size - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Misconceptions around null bitmap size - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-05-04T14:47:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T03:43:42+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/\",\"name\":\"Misconceptions around null bitmap size - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-05-04T14:47:00+00:00\",\"dateModified\":\"2013-01-02T03:43:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Misconceptions around null bitmap size\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Misconceptions around null bitmap size - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/","og_locale":"en_US","og_type":"article","og_title":"Misconceptions around null bitmap size - Paul S. Randal","og_description":"I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/","og_site_name":"Paul S. Randal","article_published_time":"2009-05-04T14:47:00+00:00","article_modified_time":"2013-01-02T03:43:42+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/","name":"Misconceptions around null bitmap size - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-05-04T14:47:00+00:00","dateModified":"2013-01-02T03:43:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-null-bitmap-size\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Misconceptions around null bitmap size"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=875"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/875\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}