Sometimes, the identity values are much higher when you delete the huge records. So there may be occassion you may come across to reset the identity.
USE TEST1
GO
CREATE TABLE [dbo].T_Replication
(
ID [int] NOT NULL identity(1,1) CONSTRAINT [PK_TT_Replication] PRIMARY KEY CLUSTERED,
[Name] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Company] [varchar](50) NULL,
[Submitdate] [datetime] NULL,
[EmailID] [varchar](100) NULL,
)
GO
INSERT INTO [Test1].[dbo].[T_Replication]
([Name]
,[LastName]
,[Company]
,[Submitdate]
,[EmailID])
VALUES
('Adams'
,'Joe'
,'Joe Company'
,getdate()
,'Adams.Joe@yahoo.com')
GO
INSERT INTO [Test1].[dbo].[T_Replication]
([Name]
,[LastName]
,[Company]
,[Submitdate]
,[EmailID])
VALUES
('John'
,'Wilkinson'
,'ABC Company'
,getdate()
,'John.Wilkinson@yahoo.com')
GO
INSERT INTO [Test1].[dbo].[T_Replication] ([Name] ,[LastName] ,[Company] ,[Submitdate] ,[EmailID]) VALUES ('John' ,'Wilkinson' ,'ABC Company' ,getdate() ,'John.Wilkinson@yahoo.com')
DELETE FROM T_REPLICATION WHERE ID=3
Now in order to insert the deleted ID for the following records, you need to reset the identity to 3 by executing the below script. Otherwise, the insertion will follow with the identity value of 4.
Script to Reset:
DBCC CHECKIDENT(T_REPLICATION,RESEED,2)
No comments:
Post a Comment