Delete TB where ID> 100
DBCC checkident ('tb', reseed, 1)
DBCC checkident ('tb', reseed)
-- The result sets the auto-incrementing seed of the TB table to the maximum value of the ID column in the current table.
-- Add the data again and the number will continue.
-- Test
-- Create a test environment
If object_id ('tb') is not null drop table TB
Go
Create Table TB
(
Id int identity,
Code varchar (10 ),
Constraint pk_tb primary key (ID)
)
Go
-- Insert Test Data
Declare @ I int
Set @ I = 1
While @ I <10
Begin
Insert TB select 'A' + ltrim (@ I)
Set @ I = @ I + 1
End
-- Query
Select * from TB
-- Result
/*
ID code
---------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 A7
8 A8
9 A9
(9 rows affected)
*/
-- 1 delete part
Delete TB where ID> 5
DBCC checkident ('tb', reseed, 1)
DBCC checkident ('tb', reseed)
/*
Check the ID information: the current ID value is '9' and the current column value is '1 '.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
Check the ID information: the current ID value is '1' and the current column value is '5 '.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
*/
-- Insert Test Data
Declare @ I int
Set @ I = 1
While @ I <10
Begin
Insert TB select 'A' + ltrim (@ I)
Set @ I = @ I + 1
End
-- Query
Select * from TB
/*
ID code
---------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A1
7 A2
8 A3
9 A4
10 A5
11 A6
12 A7
13 A8
14 A9
(14 rows affected)
*/