{"id":3615,"date":"2012-06-27T14:41:07","date_gmt":"2012-06-27T14:41:07","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/database-ids\/"},"modified":"2016-07-28T10:50:53","modified_gmt":"2016-07-28T10:50:53","slug":"database-ids","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/database-ids\/","title":{"rendered":"Database IDs"},"content":{"rendered":"<p>Just a quick post, mainly to test out the new blog format but related to a question on the #sqlhelp hashtag.<\/p>\n<p>The question came from Justin Dearing (<a href=\"http:\/\/twitter.com\/zippy1981\">@zippy1981<\/a>) as:<\/p>\n<p>So I take it database_id isn&#8217;t an ever incrementing value. <a href=\"http:\/\/twitter.com\/search?q=%23sqlhelp\">#<strong>sqlhelp<\/strong><\/a><\/p>\n<p>When a new database is created it is given the lowest available ID. This either is in a gap in IDs where a database has been dropped or the database ID is incremented by one from the highest current ID if there are no gaps to fill.<\/p>\n<p>To see this in action, connect to your sandbox server and try this:<\/p>\n<p> USE MASTER GO<\/p>\n<p>CREATE DATABASE cherry GO<\/p>\n<p>USE cherry GO<\/p>\n<p>SELECT DB_ID() GO CREATE DATABASE grape GO<\/p>\n<p>USE grape GO<\/p>\n<p>SELECT DB_ID() GO<\/p>\n<p>CREATE DATABASE melon GO<\/p>\n<p>USE melon GO<\/p>\n<p>SELECT DB_ID() GO<\/p>\n<p>USE MASTER GO<\/p>\n<p>DROP DATABASE grape GO<\/p>\n<p>CREATE DATABASE kiwi GO USE kiwi GO<\/p>\n<p>SELECT DB_ID() GO USE MASTER GO<\/p>\n<p>DROP DATABASE cherry DROP DATABASE melon DROP DATABASE kiwi<\/p>\n<p>You should get an incrementing series of database IDs as the databases are created until the last one where the new database gets allocated the ID that is missing because one was dropped.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Just a quick post, mainly to test out the new blog format but related to a question on the #sqlhelp hashtag. The question came from Justin Dearing (@zippy1981) as: So I take it database_id isn&#8217;t an ever incrementing value. #sqlhelp When a new database is created it is given the lowest available ID. This either&#8230;&hellip;<\/p>\n","protected":false},"author":101210,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-3615","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101210"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3615"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3615\/revisions"}],"predecessor-version":[{"id":42180,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3615\/revisions\/42180"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3615"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}