RESEED Not Starting From Number 1

Question

I need the Identity field "ID" to always start with 1 but it starts with the next sequential number.
For example: when query is run the first time, it returns 5 records with "ID" a, 2, 3, 4, 5. But when query is run the next time, it starts the "ID" with 6, 7, 8, 9, 10.
How do I query to start the "ID" column with 1?

SET IDENTITY_INSERT MyTable ON  
GO  
DBCC CHECKIDENT (MyTable, RESEED, 1)  

Answer 1

The way RESEED works is documented in the DBCC CHECKIDENT documentation. The result of DBCC CHECKIDENT(MyTable, RESEED, 1) depends on what has happened to that table in the past. If the table has never had any rows in it or if the table was truncated and the table has never had any rows in it since it was truncated, then the next row to be inserted will (usually) get an identity value of 1. But if the table has previously had rows in it, the next value after the RESEED will be 1 + the increment value. Since the increment value is almost always set to 1 (the default), the next row you insert will get an identity value of 2. To see how this works, you can run

Create Table MyTable(Id int primary key Identity, OtherData varchar(50));  
Insert MyTable(OtherData) Values('First');  
Insert MyTable(OtherData) Values('Second');  
Insert MyTable(OtherData) Values('Third');  
Select * From MyTable;  
  
Delete From MyTable;  
DBCC Checkident (MyTable, RESEED, 1);  
Insert MyTable(OtherData) Values('Reseeded to 1, this identity value will be 2');  
Select * From MyTable;  
  
Delete From MyTable;  
DBCC Checkident (MyTable, RESEED, 0);  
Insert MyTable(OtherData) Values('Reseeded to 0, this identity value will be 1');  
Select * From MyTable;  
  
Truncate Table MyTable;  
DBCC Checkident (MyTable, RESEED, 1);  
Insert MyTable(OtherData) Values('Reseeded to 1, this identity value will be 1');  
Select * From MyTable;  
  
go  
Drop Table MyTable;  
  

Tom

Answer 2

but it starts with the next sequential number.

Of course, that's how IDENTITY works.

But when query is run the next time

What for a query, I don't see one in your post?

How do I query to start the "ID" column with 1?

Why? It's a simply ID for internal use, the values don't matter, can be anything.

Answer 3

Isn't RESEED to have identity values reset or start at new predefined value by using DBCC CHECKIDENT?
If the answer is "yes" then why can't I set the value of ID to 1 when query runs the next time?

I do see that my original post is misleading; please let me correct it.

My table "MyTable" has 2 columns; "ID" and "CustName"
In my query; first I delete all records from "MyTable" then I RESEED and Insert records.

Answer 4

There are many reasons for identity fields not not be in sequence. IDENTITY fields are not guaranteed to be sequential. Whatever you are trying to do with them, you need to rethink, instead of using reseed.

Answer 5

Then, what's the purpose of RESEED if it does not INSERTs the Identity column starting at 1?
DBCC CHECKIDENT (MyTable, RESEED, 1)

I do understand that there are many different ways to run a counter but this RESEED has not been making sense to me and I thought to post my question here to better understand it.