Pages

Friday, January 16, 2015

Reseeding/reset the identity of table.

Generally, on inserting the new data into table we get next seed value for identity column. When a row is deleted, we cannot use that identity value again. We can resolve it by reseeding the identity of table as follows:

1. SELECT * FROM Sample_Data

​Number​Value
​1​First
​2​First
​3​First
​4​First
​5​First
​6​First
​7​First
 
2. DELETE FROM Sample_Data WHERE Number > 4

​Number​Value
​1​​First
​2​​First
​3​​First
​4​​First
 
3. DECLARE @MaxID INT
SELECT @MaxID=MAX(Number) FROM Sample_Data
DBCC CHECKIDENT('Sample_Data',RESEED,@MaxID)

GO

RESULT:-
Checking identity information: current identity value '16'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
4. INSERT INTO Sample_Data(Value) VALUES('Second')
   Go 3

​​Number​​Value
1​​​​First
2​​​​First
3​​​​First
4​​​​First
5​​Second
​6​Second
7​​Second

No comments:

Post a Comment