https://www.tutorialsteacher.com/_next/static/chunks/pages/article…lumn-value-in-sqlserver-7da2101ad1651a6e.js

1(self.webpackChunk_N_E=self.webpackChunk_N_E||[]).push([[16621],{18704:function(e,a,s){(window.__NEXT_P=window.__NEXT_P||[]).push(["/articles/reset-identity-column-value-in-sqlserver",function(){return s(77161)}])},74703:function(e,a,s){"use strict";var l=s(85893),t=s(40645),n=s.n(t),i=s(25675),c=s.n(i);a.Z=e=>{let{src:a,alt:s="",height:t,width:i,unoptimized:r=!1,fill:d=!0,sizes:o="(max-width: 480px) 60vw, (max-width: 640px) 50vw, (max-width: 1920px) 50vw, 33vw"}=e;return(0,l.jsxs)("div",{className:n().dynamic([["f343c938d3dd6bc8",[t?"".concat(t,"px"):"160px",t?"".concat(t,"px"):"250px",t?"".concat(t,"px"):"300px"]]])+" position-relative text-center body-image cursor-pointer",children:[(0,l.jsx)(c(),{src:a,alt:s,fill:d,sizes:o,height:t,width:i,unoptimized:r}),(0,l.jsx)(n(),{id:"f343c938d3dd6bc8",dynamic:[t?"".concat(t,"px"):"160px",t?"".concat(t,"px"):"250px",t?"".concat(t,"px"):"300px"],children:".body-image.__jsx-style-dynamic-selector{position:relative;width:100%;height:".concat(t?"".concat(t,"px"):"160px",";padding:0}.cursor-pointer.__jsx-style-dynamic-selector{cursor:pointer}@media(min-width:640px){.body-image.__jsx-style-dynamic-selector{height:").concat(t?"".concat(t,"px"):"250px","}}@media(min-width:1024px){.body-image.__jsx-style-dynamic-selector{height:").concat(t?"".concat(t,"px"):"300px","}}")})]})}},77161:function(e,a,s){"use strict";s.r(a);var l=s(85893),t=s(9008),n=s.n(t),i=s(74703);a.default=()=>(0,l.jsxs)(l.Fragment,{children:[(0,l.jsxs)(n(),{children:[(0,l.jsx)("title",{children:"Reset Identity Column in SQL Server"}),(0,l.jsx)("meta",{property:"og:title",content:"Reset Identity Column in SQL Server"}),(0,l.jsx)("meta",{name:"keywords",content:"reset identity column, sql server"}),(0,l.jsx)("meta",{name:"description",content:"Here you will learn how to reset value of identity column in a table."})]}),(0,l.jsxs)("article",{children:[(0,l.jsx)("h1",{children:"Reset Identity Column"}),(0,l.jsxs)("p",{children:["Here you will learn how to reset value of ",(0,l.jsx)("a",{href:"/sqlserver/identity-column",target:"_blank",children:"identity column"})," in a table."]}),(0,l.jsx)("p",{children:"In SQL Server, an identity column is used to auto-increment a column. It is useful in generating a unique number for primary key columns, where the value is not important as long as it is unique."}),(0,l.jsxs)("p",{children:["The following CREATE TABLE statement declares ",(0,l.jsx)("code",{children:"EmpID"})," as the identity column."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: Create Table with Identity Column"}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"CREATE TABLE Employee( [EmpID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL);"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsxs)("p",{children:["In the above CREATE TABLE SQL statement, ",(0,l.jsx)("code",{children:"EmpID"})," is the IDENTITY column with seed and increment at 1. So, whenever a new row is inserted, the ID will be incremented by 1."]}),(0,l.jsxs)("p",{children:["Now, let's insert a row into the new ",(0,l.jsx)("code",{children:"Employee"})," table."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: "}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"INSERT INTO Employee VALUES ('Aparna' , 'Anand')"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsx)("p",{children:"The above statement will insert the following record."}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity2.webp",alt:"",lazyLoad:!0}),(0,l.jsxs)("p",{children:["As you can see, the ",(0,l.jsx)("code",{children:"EmpID"})," value is 1 in the first record. If you insert another record then it will be 2 and so on."]}),(0,l.jsx)("h2",{children:"Reset IDENTITY column values"}),(0,l.jsxs)("p",{children:["For any reason, if an insert fails or is rolled back, the ",(0,l.jsx)("code",{children:"EmpID"})," number that was generated is lost and there will be gaps in the ",(0,l.jsx)("code",{children:"EmpID"})," column. In some cases, this can be ignored and the gaps will not make a difference. But there could be instances when it is necessary to not have gaps in the IDENTITY column. In such cases, you can reset the identity column."]}),(0,l.jsx)("p",{children:"Let's execute the following statement which will raise an error because it tries to enter NULL in the NOT NULL column."}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: "}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"INSERT INTO Employee VALUES('Ron' , NULL);"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity3.webp",alt:"",lazyLoad:!0}),(0,l.jsx)("p",{children:"Now, execute the valid insert statement as shown below"}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: "}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"INSERT INTO Employee VALUES ('Ron' , 'Kennedy')"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsxs)("p",{children:["The above statement will insert a row where the ",(0,l.jsx)("code",{children:"EmpID"})," value will be 3 and not 2, as shown below."]}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity5.webp",alt:"",lazyLoad:!0}),(0,l.jsxs)("p",{children:["As you can see, the number 2 is missing in the ",(0,l.jsx)("code",{children:"EmpID"})," column. To check the current identity value for the table and to reset the IDENTITY column, use the DBCC CHECKIDENT command."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsx)("div",{className:"card-header example-header",children:(0,l.jsx)("div",{className:"example-caption",children:"Syntax:"})}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"DBCC CHECKIDENT(table_name [,NORESEED | RESEED[, new_reseed_value]]"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsx)("h4",{children:"Parameters:"}),(0,l.jsxs)("ol",{children:[(0,l.jsx)("li",{children:"table_name: The table for which to reset the identity column. The specified table should have an IDENTITY column."}),(0,l.jsx)("li",{children:"NORESEED: Specifies that the current identity value should not be changed."}),(0,l.jsx)("li",{children:"RESEED: Specifies that the current identity values should be changed."}),(0,l.jsx)("li",{children:"new_reseed_value: New value to be used as the current value of the identity column."})]}),(0,l.jsx)("p",{children:"Note: To change the existing seed value and to reseed the existing rows, you have to drop the identity column and recreate it with the new seed value."}),(0,l.jsx)("p",{children:"To reset the identity column to the starting seed, you have to delete the rows, reseed the table and insert all the values again."}),(0,l.jsx)("p",{children:"When there are many rows, create a temporary table with all the columns and values from the original table except the identity column. Truncate the rows from the main table. Copy the rows back to the main table from the temporary table."}),(0,l.jsxs)("p",{children:["In our example, delete the row with ID 3, reseed the ",(0,l.jsx)("code",{children:"Employee"})," table with 1 so that the next value inserted will be 2 which is the required identity number, and insert the last value again."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: Reset Identity Column"}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"DELETE FROM Employee WHERE EmpID = 3; -- delete  a row DBCC CHECKIDENT ('Employee', RESEED, 1); -- reset identity column to 1 INSERT INTO Employee VALUES ('Ron', 'Kennedy'); -- insert row again"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsxs)("p",{children:["Now, when you select rows from the ",(0,l.jsx)("code",{children:"Employee"})," table, the ",(0,l.jsx)("code",{children:"EmpID"})," column is in order."]}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity7.webp",alt:"",lazyLoad:!0}),(0,l.jsxs)("p",{children:["Now, insert a few more values into the ",(0,l.jsx)("code",{children:"Employee"})," table."]}),(0,l.jsx)("div",{className:"card code-panel-without-title",children:(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"INSERT INTO dbo.Employee VALUES ('Jeff', NULL) INSERT INTO dbo.Employee VALUES ('Jeff', 'Brown') INSERT INTO dbo.Employee VALUES ('Maria', 'Blight')"})})})}),(0,l.jsxs)("p",{children:["The first insert will fail because of the NULL value, but the next two statements will insert values in the ",(0,l.jsx)("code",{children:"Employee"})," table."]}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity8.webp",alt:"",lazyLoad:!0}),(0,l.jsxs)("p",{children:["As you can see from the above image, ",(0,l.jsx)("code",{children:"EmpID"})," is generated for every row inserted. For the invalid row, an ",(0,l.jsx)("code",{children:"EmpID"})," is generated but not used which leaves a gap in the ",(0,l.jsx)("code",{children:"EmpID"})," after 2."]}),(0,l.jsxs)("p",{children:["To reset the ",(0,l.jsx)("code",{children:"EmpID"}),", we can create a temp table and transfer the data into that temp table; truncate the main ",(0,l.jsx)("code",{children:"Employee"})," table, and insert all rows again."]}),(0,l.jsxs)("p",{children:["Use the following query to create a new temp table and copy all rows from the ",(0,l.jsx)("code",{children:"Employee"})," table to the temp table. The temp table will not have the identity column (",(0,l.jsx)("code",{children:"EmpID"}),"). It will have all other columns from the main ",(0,l.jsx)("code",{children:"Employee"})," table."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: Insert Rows into Temp Table"}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"CREATE TABLE [dbo].[TempEmployee]( [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL ); INSERT INTO TempEmployee SELECT FirstName, LastName FROM Employee;"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsxs)("p",{children:["The ",(0,l.jsx)("code",{children:"TempEmployee"})," table will be populated with all rows from the ",(0,l.jsx)("code",{children:"Employee"})," table"]}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity10.webp",alt:"",lazyLoad:!0}),(0,l.jsxs)("p",{children:["Now truncate the ",(0,l.jsx)("code",{children:"Employee"})," table to delete all rows. To reset the identity column, you have to truncate the table."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: Delete All Records"}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"TRUNCATE table Employee;"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsxs)("p",{children:["The above statement will delete all the records from the ",(0,l.jsx)("code",{children:"Employee"})," table."]}),(0,l.jsxs)("p",{children:["Now, insert all records from the ",(0,l.jsx)("code",{children:"TempEmployee"})," table back to the ",(0,l.jsx)("code",{children:"Employee"})," table, as shown below."]}),(0,l.jsxs)("div",{className:"card code-panel",children:[(0,l.jsxs)("div",{className:"card-header example-header",children:[(0,l.jsx)("div",{className:"example-caption",children:"Example: "}),(0,l.jsxs)("button",{className:"copy-btn pull-right",title:"Copy example code",children:[(0,l.jsx)("i",{className:"fa fa-copy"})," Copy"]})]}),(0,l.jsx)("div",{className:"panel-body",children:(0,l.jsx)("pre",{className:"language-sql",children:(0,l.jsx)("code",{children:"INSERT INTO Employee (FirstName, LastName) SELECT FirstName, LastName FROM TempEmployee;"})})}),(0,l.jsx)("div",{className:"card-footer example-footer"})]}),(0,l.jsxs)("p",{children:["Now, select all rows from the ",(0,l.jsx)("code",{children:"Employee"})," table. The identity column ",(0,l.jsx)("code",{children:"EmpID"})," will be reset and displayed in sequence without any gap."]}),(0,l.jsx)(i.Z,{src:"/images/articles/sqlserver/reset-identity14.webp",alt:"",lazyLoad:!0}),(0,l.jsx)("p",{children:"Thus, you can reset the identity column values."})]})]})}},function(e){e.O(0,[40645,92888,49774,40179],function(){return e(e.s=18704)}),_N_E=e.O()}]);