CODEWITHSUNDEEP
sql-server
Viktor38
Viktor38

Reputation: 23

Id identity int doesn't start from 1?

In SQL Server Management Studio I created several tables and in id I used identity(1,1) with constraint primary key clustered Id asc. When I was insert values in tables and after that I saw the column with id in every table starting with another number like 8, 5, 9 etc, but not with 1 as it should be. I didn't have this problem before. Is it possible because first I connected tables with foreign key and after that I started to insert values? Here is the code:

create table [dbo].[Semester]
(
     [Id] [int] identity(1,1) not null,
     [Semester] [tinyint] null,
     [StartDay] [date] not null,
     [EndDate] [date] not null,

     constraint [PK_Semester] 
         primary key clustered ([Id] asc)
)
go

insert into [dbo].[Subject] ([Name], [Days])
values ('Intro in programming', 20)

and the result is:

Id Name Days SemesterId
1 4 Intro in programming 20 null

I don't have insert value in SemesterId because in that table like in every tables is the same problem and have exception doesn't match with Id in Semester table with SemesterId, because in Semester table column Id is 6 and whenever I delete them and re-create them, Id's has difference numbers, but not start from 1 and doesn't ascending in order.

Upvotes: 1

Views: 4029

Answers (2)

Zeynep Topçu
Zeynep Topçu

Reputation: 87

When you send an INSERT query to the SQL Server, the SQL Server generates an ID for this query. If the query is successful, you will see that ID you assigned in the table. If the query fails, it will not save the same ID for another query. SQL Server generates a new ID on the next query.

Namely,

You probably made incorrect entries in column 'SemesterId' of table 'Subject'. As far as I know there is no problem. IDENTITY columns increase the value even if the INSERT to table is not performed. But If you say I want to see sequentially, you set the IDENTITY property to -No-, in this case you need to assign an ID for each record. This is a bit of a problem for tables with many records.

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

you can use command below to see what is current seed of your table identity value:

DBCC CHECKIDENT('Subject');

with command below you can reseed your identity value if you have no data in the table otherwise there are some exceptions ( Read Microsoft Doc)

DBCC CHECKIDENT ('table_name', RESEED, new_value);

In your case I assume you have inserted some rows and then you have deleted , you can use

TRUNCATE TABLE tablename

to truncate table as well as reset the seed identiry

Upvotes: 0

Related Questions