{"id":581,"date":"2013-06-05T09:17:35","date_gmt":"2013-06-05T16:17:35","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=581"},"modified":"2017-04-13T09:19:14","modified_gmt":"2017-04-13T16:19:14","slug":"the-nuance-of-dbcc-checkident-that-drives-me-crazy","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/","title":{"rendered":"The Nuance of DBCC CHECKIDENT That Drives Me Crazy"},"content":{"rendered":"<p>When I put together my DBCC presentation a couple years ago I created a demo for the <a title=\"DBCC CHECKIDENT\" href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-checkident-transact-sql\">CHECKIDENT <\/a>command.\u00a0 I had used it a few times and figured it was a pretty straight-forward command.\u00a0 In truth, it is, but there is one thing that I don\u2019t find intuitive about it.\u00a0 And maybe I\u2019m the only one, but just in case, I figured I\u2019d write a quick post about it.<\/p>\n<p>CHECKIDENT is used to check the current value for an identity column in a table, and it can also be used to change the identity value.\u00a0 The syntax is:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC CHECKIDENT\r\n (\r\n   table_name\r\n     &#x5B;, {\u00a0NORESEED | { RESEED &#x5B;,\u00a0new_reseed_value ] }\u00a0}\u00a0]\r\n )\r\n&#x5B; WITH NO_INFOMSGS ]\r\n<\/pre>\n<p>To see it in action, let\u2019s connect to a copy of the AdventureWorks2012 database and run it against the SalesOrderHeader table:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;AdventureWorks2012];\r\nGO\r\n\r\nDBCC CHECKIDENT ('Sales.SalesOrderHeader');\r\n<\/pre>\n<p>In the output we get:<\/p>\n<p><code>Checking identity information: current identity value '75123', current column value '75123'.<br \/>\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<\/code><\/p>\n<p>Hooray, seems pretty basic, right?\u00a0 Well, did you know that running the command as I did above <i>can<\/i> change the identity seed if the identity value and column value do not match? \u00a0This is what I meant initially when I said it wasn\u2019t intuitive.\u00a0 I didn\u2019t include any options with the command, therefore I do <i>not<\/i> expect it to make any changes.\u00a0 In fact, you have to <i>include<\/i> an option to ensure you do <i>not <\/i>make a change.\u00a0 Let\u2019s take a look.<\/p>\n<p>First we\u2019ll create a table with an identity column and populate it with 1000 rows:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;AdventureWorks2012];\r\nGO\r\n\r\nCREATE TABLE &#x5B;dbo].&#x5B;identity_test] (\r\n   id INT IDENTITY (1,1),\r\n   info VARCHAR(10));\r\nGO\r\n\r\nSET NOCOUNT ON;\r\nGO\r\n\r\nINSERT INTO &#x5B;dbo].&#x5B;identity_test] (\r\n   &#x5B;info]\r\n   )\r\n   VALUES ('test data');\r\nGO 1000\r\n<\/pre>\n<p>Now we\u2019ll run CHECKIDENT, as we did above:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC CHECKIDENT ('dbo.identity_test');\r\n<\/pre>\n<p><code>Checking identity information: current identity value '1000', current column value '1000'.<br \/>\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<\/code><\/p>\n<p>Our results are what we expect.\u00a0 Now let\u2019s reseed the identity value down to 10:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC CHECKIDENT ('dbo.identity_test', RESEED, 10);\r\n<\/pre>\n<p><code>Checking identity information: current identity value '1000'.<br \/>\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<\/code><\/p>\n<p>The output doesn\u2019t tell us specifically that the identity has been reseeded, so we\u2019ll run CHECKIDENT again, but this time with the NORESEED option (different than what we ran initially):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDBCC CHECKIDENT ('dbo.identity_test', NORESEED);\r\n<\/pre>\n<p><code>Checking identity information: current identity value '10', current column value '1000'.<br \/>\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<\/code><\/p>\n<p>Now we can see that the identity value and the current column are different, and because we included the NORESEED option, nothing happened.\u00a0 And this is my point: if you do <i>not<\/i> include the NORESEED option, if the identity and column values do not match, the identity will reseed:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n--first execution\r\nDBCC CHECKIDENT ('dbo.identity_test');\r\nPRINT ('first execution done');\r\n\r\n--second execution\r\nDBCC CHECKIDENT ('dbo.identity_test');\r\nPRINT ('second execution done');\r\n<\/pre>\n<p><code>Checking identity information: current identity value '10', current column value '1000'.<br \/>\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<br \/>\nfirst execution done<\/code><\/p>\n<p><code>Checking identity information: current identity value '1000', current column value '1000'.<br \/>\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<br \/>\nsecond execution done<\/code><\/p>\n<p>So just in case I\u2019m not the only one for whom this isn\u2019t obvious: <b>Make sure to include the NORESEED option when running DBCC CHECKIDENT<\/b>.\u00a0 Most of the time, the identity value probably matches the value for the column.\u00a0 But that one time where it doesn\u2019t, you may not want to reseed it\u2026at least not right away.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When I put together my DBCC presentation a couple years ago I created a demo for the CHECKIDENT command.\u00a0 I had used it a few times and figured it was a pretty straight-forward command.\u00a0 In truth, it is, but there is one thing that I don\u2019t find intuitive about it.\u00a0 And maybe I\u2019m the only [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[33,34],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Nuance of DBCC CHECKIDENT That Drives Me Crazy - Erin Stellato<\/title>\n<meta name=\"description\" content=\"The DBCC CHECKIDENT command is pretty straight-forward-but did you know that executing the syntax without any options can reset the identity value? Read on!\" \/>\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\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Nuance of DBCC CHECKIDENT That Drives Me Crazy - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"The DBCC CHECKIDENT command is pretty straight-forward-but did you know that executing the syntax without any options can reset the identity value? Read on!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2013-06-05T16:17:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:14+00:00\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/\",\"name\":\"The Nuance of DBCC CHECKIDENT That Drives Me Crazy - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2013-06-05T16:17:35+00:00\",\"dateModified\":\"2017-04-13T16:19:14+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"The DBCC CHECKIDENT command is pretty straight-forward-but did you know that executing the syntax without any options can reset the identity value? Read on!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Nuance of DBCC CHECKIDENT That Drives Me Crazy\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Nuance of DBCC CHECKIDENT That Drives Me Crazy - Erin Stellato","description":"The DBCC CHECKIDENT command is pretty straight-forward-but did you know that executing the syntax without any options can reset the identity value? Read on!","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\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/","og_locale":"en_US","og_type":"article","og_title":"The Nuance of DBCC CHECKIDENT That Drives Me Crazy - Erin Stellato","og_description":"The DBCC CHECKIDENT command is pretty straight-forward-but did you know that executing the syntax without any options can reset the identity value? Read on!","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/","og_site_name":"Erin Stellato","article_published_time":"2013-06-05T16:17:35+00:00","article_modified_time":"2017-04-13T16:19:14+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/","name":"The Nuance of DBCC CHECKIDENT That Drives Me Crazy - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2013-06-05T16:17:35+00:00","dateModified":"2017-04-13T16:19:14+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"The DBCC CHECKIDENT command is pretty straight-forward-but did you know that executing the syntax without any options can reset the identity value? Read on!","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/the-nuance-of-dbcc-checkident-that-drives-me-crazy\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"The Nuance of DBCC CHECKIDENT That Drives Me Crazy"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/581"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=581"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/581\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}