{"id":1136,"date":"2019-12-26T20:55:11","date_gmt":"2019-12-26T20:55:11","guid":{"rendered":"http:\/\/blog.silverterra.net\/?p=1136"},"modified":"2019-12-26T22:05:29","modified_gmt":"2019-12-26T22:05:29","slug":"mssql-custom-start-value-for-identity-column","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=1136","title":{"rendered":"mssql – custom start value for identity column"},"content":{"rendered":"

You can give an initial value for the identity column when you are creating the new table.<\/p>\n

\r\nCREATE TABLE ta_tester (\r\n\tCol1 int IDENTITY([startValue],[increment]) NOT NULL,\r\n\tCol2 varchar(100)\r\n)\r\n<\/pre>\n
If you want an identity column which one is starts from zero<\/p>\n
\r\nCREATE TABLE ta_tester (\r\n\tCol1 int IDENTITY(0,1) NOT NULL,\r\n\tCol2 varchar(100)\r\n)\r\n<\/pre>\n
If the table already created you can override the identity column startValue with the following command:<\/p>\n
\r\nDBCC CHECKIDENT ( [tableName], RESEED, [startValue])\r\n<\/pre>\n
where the<\/p>\n
tableName<\/b> is the target table name
\nstartValue<\/b> is not the start value! This is the startValue – 1<\/p>\n
it’s meaning if you want to start the identity from zero the correct command will be like this:<\/p>\n
\r\nDBCC CHECKIDENT ( [tableName], RESEED, -1)\r\n<\/pre>\n
nota bene<\/b>
\nIf the target table contains data and you changed the identity column actual value to a smaller value you will receive a Violation of PRIMARY KEY constraint<\/b> error when you try to insert a new record.<\/p>\n
In this case you need to set the start value to the last record identity or higher.<\/p>\n
If you don’t have primary key<\/b> constraint on the identity column the server will give the next number for the new row – which one ‘may be’ was previously given already! So in this case you have two records in the table with the same identity…<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
You can give an initial value for the identity column when you are creating the new table. If you want an identity column which one is starts from zero If the table already created you can override the identity column startValue with the following command: where the tableName is the target table name startValue is […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-1136","post","type-post","status-publish","format-standard","hentry","category-mssql"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1136","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1136"}],"version-history":[{"count":5,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1136\/revisions"}],"predecessor-version":[{"id":1141,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1136\/revisions\/1141"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1136"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1136"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}