{"id":4808,"date":"2017-10-03T08:39:51","date_gmt":"2017-10-03T15:39:51","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4808"},"modified":"2017-10-04T09:49:40","modified_gmt":"2017-10-04T16:49:40","slug":"sqlskills-sql101-running-out-of-ints-and-bigints","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/","title":{"rendered":"SQLskills SQL101: Running out of ints and bigints"},"content":{"rendered":"

As\u00a0Kimberly blogged about earlier this year<\/a>, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

We’re teaching a class this week, and one topic that always comes up is using an int identity<\/em> as a clustering key and the possibility of running out of integers. Depending on your insert volume, this might be quite likely, as an int<\/em> can only store 2^32 (^ = \u2018to the power\u2019) or about 4 billion values, between -2^31 and 2^31-1.<\/p>\n

Imagine that you have a theoretical system that can create a thousand data rows per second. Using an int identity<\/em> value increasing by 1 and starting at 1, you\u2019ll run out of values when the value hits 2^31-1 and tries to insert the next value. Let\u2019s simplify the math by just saying that 2^31 is the limit. With a thousand values per second, that would mean 2^31 \/ 1,000 = 2.15 million seconds or just\u00a0under 25 days. While many of you don\u2019t sustain 1,000 rows per second, this is still a very problematic limitation.<\/p>\n

One solution is to use a bigint identity<\/em> as the key. This can store 2^64 or about 18.5 quintillion (18.5 billion billion) values, between -2^63 and 2^63-1.<\/p>\n

Every so often someone asks whether it\u2019s possible to run out of bigint<\/em> values. My answer is no. Well, technically yes, there is a limit, but in practical terms the answer is no.<\/p>\n

Now imagine that you have a theoretical system that can create a million data rows per second, with a bigint identity<\/em> value increasing by 1 and starting at 1. You\u2019ll run out of values when the value hits 2^63-1 and tries to insert the next value. With a million values per second, that would mean 2^63 \/ 10^6 = 9.2 trillion seconds or approximately 292.5 thousand years. And by then it\u2019s someone else\u2019s problem… :-)\u00a0And that’s only for half the possible range of\u00a0bigint<\/em> values.<\/p>\n

Now what about the storage for those values? Doing a quick test of a heap with a single bigint identity<\/em> column shows me that I can get 453 rows per 8KB data file page (don\u2019t forget the record overhead, slot array overhead, and that the heap pages won\u2019t be filled completely because of the way the free space caching and searching works). A terabyte of data would store roughly 61 billion rows.<\/p>\n

So with 1 million rows per second, you\u2019ll be generating 1 million x 3,600 (seconds in an hour) x 24 (hours in a day) = 86.4 billion rows per day, so you\u2019ll need about 1.4 terabytes of new storage per day. If you’re using the\u00a0bigint identity<\/em> as a cluster key, each row needs new space, so you\u2019ll need almost exactly 0.5 petabytes of new storage every year.<\/p>\n

At that rate, actually running out of bigint<\/em> values AND storing them would take roughly 150 thousand petabytes. This is clearly impractical \u2013 especially when you consider that storing *just* a bigint<\/em> is pretty pointless \u2013 you\u2019d be storing a bigint<\/em> and some other data too \u2013 probably doubling the storage necessary, at least.<\/p>\n

Why is this interesting? We\u2019ve had a number of clients over the years that didn\u2019t consider their data volume and designed a schema using int<\/em> keys instead of bigint<\/em> keys. When the inevitable happened and they ran out of int<\/em> values, the process of changing to a bigint<\/em> key was quite painful \u2013 as there\u2019s no really easy, space and log efficient way to do it once you have the 2 billion rows, and especially if constraints are involved, and application changes need to be made to allow 8-byte\u00a0values instead of 4-byte values in result sets.<\/p>\n

A common stop-gap solution people use when they run out of int<\/em> values is to just reset the identity seed to -2^31 and then set the increment to 1. As a short-term solution this does work, especially if the int<\/em> key is a surrogate key and doesn’t have a business meaning, but it\u2019s not ideal as a long term solution as you\u2019ll only run out again once the int<\/em> key kits -2^31. Ultimately, you\u2019ll need to make the int<\/em> to bigint<\/em> change.<\/p>\n

Summary: make sure that when you\u2019re designing a new schema, you think through the maximum values required and pick appropriate data types then and there. Changing data types can be very painful once the system has been in production for a while and there\u2019s a lot of data in the schema.<\/p>\n

PS If you honestly believe you’ll run out of\u00a0bigint<\/em> values, you can use a\u00a0decimal<\/em> or\u00a0numeric<\/em> value, both of which can hold -10^38 to 10^38+1. Those are really big numbers. 10^ 38 is\u00a0about 2^129, or 100 undecillion, or 2^64 times more values than a bigint<\/em> can hold. Using our million-row-per-second server, inserting 10^38 values would take 10^38 \/ 10^6 = 10^32 seconds = roughly 3,170\u00a0billion billion\u00a0years. Now if you’re concerned about *that*, the sun will have become a red giant and incinerated the Earth in about 5 billion years…<\/p>\n","protected":false},"excerpt":{"rendered":"

As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. We’re […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61,62,108],"tags":[],"class_list":["post-4808","post","type-post","status-publish","format-standard","hentry","category-misconceptions","category-on-disk-structures","category-sql101"],"yoast_head":"\nSQLskills SQL101: Running out of ints and bigints - 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\/sqlskills-sql101-running-out-of-ints-and-bigints\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Running out of ints and bigints - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. We’re […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-10-03T15:39:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-10-04T16:49:40+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\/sqlskills-sql101-running-out-of-ints-and-bigints\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/\",\"name\":\"SQLskills SQL101: Running out of ints and bigints - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-10-03T15:39:51+00:00\",\"dateModified\":\"2017-10-04T16:49:40+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Running out of ints and bigints\"}]},{\"@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":"SQLskills SQL101: Running out of ints and bigints - 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\/sqlskills-sql101-running-out-of-ints-and-bigints\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Running out of ints and bigints - Paul S. Randal","og_description":"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. We’re […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/","og_site_name":"Paul S. Randal","article_published_time":"2017-10-03T15:39:51+00:00","article_modified_time":"2017-10-04T16:49:40+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\/sqlskills-sql101-running-out-of-ints-and-bigints\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/","name":"SQLskills SQL101: Running out of ints and bigints - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-10-03T15:39:51+00:00","dateModified":"2017-10-04T16:49:40+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-running-out-of-ints-and-bigints\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Running out of ints and bigints"}]},{"@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\/4808","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=4808"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4808\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}