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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Viktor38
  • 13
  • 1
  • 6
  • 1
    The table that your `INSERT` has as target isn't the one the `CREATE` is from. – sticky bit Dec 14 '20 at 02:05
  • 1
    If you delete records the numbers aren't reused. And they usually don't need to and shouldn't do that either. The only thing `identity(1,1)` guarantees is that each value is unique. And that's the only important thing. So you shouldn't worry about that. It's all OK. – sticky bit Dec 14 '20 at 02:08
  • The reason Id is jumping is likely related to: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database – axwr Dec 14 '20 at 02:09
  • You should'n care about it. If you do, you shouldn't use an Identity column. Read more: https://zoharpeled.wordpress.com/2019/10/06/sql-servers-identity-column-misconceptions/ – Zohar Peled Dec 14 '20 at 22:04

2 Answers2

1

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.

Zeynep Topçu
  • 77
  • 6
0

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

eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • Yes it works. Thanks a lot. First I deleted every rows, then DBCC CHECKIDENT('Semester', RESEED, 0) and after that, insert values in table. – Viktor38 Dec 14 '20 at 16:12
  • great , please accept this answer as an answer to help community , – eshirvana Dec 14 '20 at 16:15