{"id":2523,"date":"2014-08-06T15:00:59","date_gmt":"2014-08-06T19:00:59","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2523"},"modified":"2014-10-15T13:20:43","modified_gmt":"2014-10-15T17:20:43","slug":"blob-overflow","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/","title":{"rendered":"How to Overflow a BLOB Value"},"content":{"rendered":"
\n

\nMicrosoft advertises that you can store 2 gigabytes in a single BLOB value. But, what does this number actually mean? If you buy a 2TB hard drive, you might actually only get 1.8TB of usable space. The same picture presents itself when you are looking at thumb drives or memory cards for your camera. This is in part due to the fact, that some people call 1000 bytes a kilobyte and some people call 1024 bytes a kilobyte. The same factor applies then to get from a kilobyte to a megabyte and again to get to a gigabyte and from there to a terabyte. The difference at the terabyte level is 1024^4 \u2013 1000^4 = 1,099,511,627,776 - 1,000,000,000,000 which is close to 100 gigabytes. But there are also other factors at play that hide behind terms like \"raw-storage\".\n<\/p>\n

\nWith all that storage size fuzziness in the industry, I thought it might be interesting to figure out how much room there really is in a SQL Server BLOB.\n<\/p>\n

BLOB Explained<\/h3>\n

\nBLOB stands for Binary Large Object. Different database systems have different definitions of how large a value has to be to qualify as a BLOB value. In SQL Server, This is really more due to the data type than to actual value size. However, the largest value you might encounter that is not a BLOB is 8000 bytes in size.\n<\/p>\n

\nThe data types in SQL Server that allow for BLOB storage are VARCHAR(MAX)<\/span>, NVARCHAR(MAX)<\/span> and VARBINARY(MAX)<\/span>. You might also still encounter the deprecated TEXT<\/span>, NTEXT<\/span> and IMAGE<\/span> legacy data types.\n<\/p>\n

\nTechnically, only the VARBINARY(MAX)<\/span> data type allows to store a BLOB, as that acronym is based on the word \"binary\". Sometimes you will find the character large objects specified as just LOB values. However, the term BLOB has been established to include any large value stored in one of the above data types.\n<\/p>\n

Testing the Two Gigabyte Boundary<\/h3>\n

\nThe most generous definition of two gigabytes is 2*1024*1024*1024 = 2,147,483,648 bytes. Let us try to create a value that size. I already created a simple table with a VARCHAR(MAX)<\/span> column and inserted a single row. With the following statement, I am going to attempt to store a 2147483648-character string in that column:\n<\/p>\n

\n[sql]\nUPDATE dbo.LOBTable SET
\n LOB = REPLICATE(CAST('x' AS VARCHAR(MAX)),2147483648);
\n[\/sql]\n<\/div>\n

\nThe statement does not have a WHERE<\/span> clause as there is only a single row in that table right now. Running this statement results in . . . . . . an error:\n<\/p>\n

\n\"a<\/a>\n<\/p>\n

\nApparently, 2 gigabytes is too much to be stored in a SQL Server BLOB data type. However, if you look at the error message, you see that we were not far off after all. The maximum number of characters allowed is just one character less. Let us try to confirm that:\n<\/p>\n

\n[sql]\nUPDATE dbo.LOBTable SET
\n LOB = REPLICATE(CAST('x' AS VARCHAR(MAX)),2147483647);<\/p>\n

SELECT LEN(LT.LOB) FROM dbo.LOBTable AS LT;
\n[\/sql]\n<\/p><\/div>\n

\nThis time we are also going to determine the length of the stored string after the update, just to make sure we did not lose any characters somewhere mysteriously. The result is very much promising:\n<\/p>\n

\n\"a<\/a>\n<\/p>\n

\nThe reason for this single missing byte is a performance optimization.\n<\/p>\n

The Missing Byte<\/h3>\n

\nYou might know that SQL Server stores every BLOB value in its own B+Tree index<\/a>. That allows SQL Server to access any sub-string directly without having to read the entire value into memory. If you look at the number of pages required to store our 2147483647-byte value on disc, you see that there are 566 additional TEXT_TREE_PAGES<\/span> that make up the B+Tree:\n<\/p>\n

\n\"count<\/a>\n<\/p>\n

\nThe actual data is stored in 267100 pages of type TEXT_MIX_PAGE<\/span> - about 8044 bytes per page.\n<\/p>\n

\nThe index key for this B+Tree index is the position of the character within the string and is expressed as a single INT value. That means that the highest position that this index can reference is the highest number that can be stored in an INT.\n<\/p>\n

\nSQL Server does not know unsigned integers, so the largest positive value that can be stored in an INT is 2^31-1 or 2,147,483,647. Therefore, SQL Server can only allow that many bytes in a single BLOB value.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server is marketed to allow 2 gigabytes of data to be stored in any of its three BLOB data types. The actual number of bytes that can be stored is 2^31-1 = 2,147,483,647. Not coincidentally, this is also the largest number that can be held by the INT data type.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2524,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,27,14,105],"tags":[215,220,214,15,108,109],"class_list":["post-2523","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","category-series","category-sql-server-internals","category-storage-wednesday","tag-blob","tag-blob-index","tag-lob","tag-sql-server","tag-storage","tag-storage-engine"],"acf":[],"yoast_head":"\nHow to Overflow a BLOB Value - sqlity.net<\/title>\n<meta name=\"description\" content=\"It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Overflow a BLOB Value - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-08-06T19:00:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-10-15T17:20:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"468\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Overflow a BLOB Value\",\"datePublished\":\"2014-08-06T19:00:59+00:00\",\"dateModified\":\"2014-10-15T17:20:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/\"},\"wordCount\":764,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/LOB_too_large.jpg\",\"keywords\":[\"BLOB\",\"BLOB Index\",\"LOB\",\"SQL Server\",\"storage\",\"storage engine\"],\"articleSection\":[\"General\",\"Series\",\"SQL Server Internals\",\"Storage Wednesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/\",\"name\":\"How to Overflow a BLOB Value - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/LOB_too_large.jpg\",\"datePublished\":\"2014-08-06T19:00:59+00:00\",\"dateModified\":\"2014-10-15T17:20:43+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/LOB_too_large.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/LOB_too_large.jpg\",\"width\":768,\"height\":468,\"caption\":\"a value too large for a BLOB\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2523\\\/blob-overflow\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Overflow a BLOB Value\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Overflow a BLOB Value - sqlity.net","description":"It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.","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:\/\/sqlity.net\/en\/2523\/blob-overflow\/","og_locale":"en_US","og_type":"article","og_title":"How to Overflow a BLOB Value - sqlity.net","og_description":"It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.","og_url":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-08-06T19:00:59+00:00","article_modified_time":"2014-10-15T17:20:43+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg","type":"image\/jpeg"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Overflow a BLOB Value","datePublished":"2014-08-06T19:00:59+00:00","dateModified":"2014-10-15T17:20:43+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/"},"wordCount":764,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg","keywords":["BLOB","BLOB Index","LOB","SQL Server","storage","storage engine"],"articleSection":["General","Series","SQL Server Internals","Storage Wednesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/","url":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/","name":"How to Overflow a BLOB Value - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg","datePublished":"2014-08-06T19:00:59+00:00","dateModified":"2014-10-15T17:20:43+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"It is advertised as 2 GB, but what is actually the largest number of bytes that can be stored in a SQL Server BLOB value? Read on to find out now.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2523\/blob-overflow\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg","width":768,"height":468,"caption":"a value too large for a BLOB"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2523\/blob-overflow\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Overflow a BLOB Value"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/LOB_too_large.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-EH","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2523"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2523\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2524"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}