CODEWITHSUNDEEP
sqlsql-server
什么神奇
什么神奇

Reputation: 11

Why my identity column value starts at 2

I use SQL Server to create a table and insert data for it, but why does an open id not 1 but 2?

create table test1
(
    id int IDENTITY(1,1),
    name varchar(255),
    age int,
    primary key (id),
    constraint check_age check (age > 0 and age <= 150)
);

The following is the result of the display.

select * from test1;

Output:

+----+--------------+-----+
| id |     name     | age |
+----+--------------+-----+
|  2 | shenmeshenqi |  20 |
|  3 | shenmeshenq  |  21 |
+----+--------------+-----+

Who can help me? Thank you

Upvotes: 1

Views: 2334

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37525

Try this:

--store values from the table in temporary table
select * into #tempTable from test1
--remove all records from the table
delete test1
--reset identity to start from 1
dbcc checkident (test1, reseed, 0);
--insert data back to the table, now the records will be numbered starting with 1
insert into test1
select * from #tempTable

Upvotes: 2

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

The below command will make the next insertion have the identity value as 1 and after that 2 and so on, thus 2 and 3 will be duplicates. You may want to just truncate the table for now as it looks like a test table.

dbcc checkident (test1, reseed, 0);

Also, you can turn onidentity insert, then insert data with value 1 as id and then turn off the identity again, if required.

SET IDENTITY_INSERT dbo.Test1 ON
SET IDENTITY_INSERT dbo.Test1 OFF

Hope it helps.

Upvotes: 0

Related Questions