I would suggest you to try to replicate it..try to reseed the column to 5 again and insert a few items and check if it happens again?
goel.ankit
I would suggest you to try to replicate it..try to reseed the column to 5 again and insert a few items and check if it happens again?
I have deleted all of my test records (although there are only 4 of them), so there is no records in the table, instead of creating record by web form, but this time, I just add one record in SQL table. so I click Server Explorer, and then select table,
then click Show Table Data, from start all of first row are null, after I add all fields except the first ID clumn. then Update the add, now the first ID column changed to 1015.
I guess if I delete it again, and add again, it will be 1016. I do not want to delete the table and create it again just becuase of this, since it may heppen again for a new created table.
Do not know how to reseed the ID to "1".
Any idea?
Rather than deleting everything in the table use
truncate table tablename
That will remove everything and re-set the seed too.
nothing wrong. its normal if you delete some record and new record will jump to biggest number ever used.
oned_gk
nothing wrong. its normal if you delete some record and new record will jump to biggest number ever used.
Hi, thanks for the help,
For my case, actually it is not the case, in my case, I have created about 15 rows of table, and deleted some 20 rows of records, so the total of rows are about less than 40 rows, since my table is just created and started to create some test data. However,
my next ID increased to 1016 which is unbelievable, since I have never created 1000 records, and I checked my codes, there are no any loop to create the table rows.
I also check the SQL table from my SQL Management Studio, can not figure out how come the ID increased suddenly.
Identity column will be increased by 1 automatically, suppose if you add 4 records and the id become 4. If you delete the last record and again you add the new record the id will become 5 only not 4..
Identity column will be increased by 1 automatically, suppose if you add 4 records and the id become 4. If you delete the last record and again you add the new record the id will become 5 only not 4..
I understand that, if you read my post carefully, but for my case, the ID is not 5, the ID is suddenly increased to 1000, again, I just started to create the new SQL table and enter the records, considering some addition and deletions, it should not increase
to 1000.
If you read somebody's posts in this thread, it seems this is the bug for SQL server db in VS2012. any idea?
It is a designed behavior based on the information from MS. Just know it and you may revist this issue after some long time with some service packs. It may or may not be fixed.
If you want to know more about this issue, you can check this (not a solution):
It is a designed behavior based on the information from MS. Just know it and you may revist this issue after some long time with some service packs. It may or may not be fixed.
If you want to know more about this issue, you can check this (not a solution):
Thanks lot for your updates, it seems this is a common issue. it is OK if the application do not use this ID key, but for my case, my web apps is using as this key as a new transaction number generator instead of creating a new one.
For example, I have a product number field some thing like PROD0001, PROD0002 etc, it combines "PROD" + numbers which increased 1 by 1, so for my case, although the product number will have some gaps between each other, but for this case, the product number
will be suddenly increased to a big number, users will ask why.
Any way, hope MS will have a fix pack soon, by the way, I am using VS2012, I think the SQL server database with VS2012 should be at least SQL 2008 RC, no sure.
Hi , thanks a lot, I am new in SQL server, could you please give me the full codes of how to implement it?
So that means, I will add this codes in my C# code whenever I add a record to SQL table,, I have to reseed the ID data? Instead of using the automatically seeding from SQL database?
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Why does my first ID suddently increase so big number?
Feb 18, 2013 03:18 PM|LINK
An identity jump 1000 is not normal. You can reset it back but you can find more information from this thread:
http://social.msdn.microsoft.com/Forums/en-IN/transactsql/thread/3d256650-0e94-4d0f-8b52-0ba6e1903215
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
AidyF
Star
9204 Points
1570 Posts
Re: Why does my first ID suddently increase so big number?
Feb 18, 2013 03:20 PM|LINK
Rather than deleting everything in the table use
truncate table tablename
That will remove everything and re-set the seed too.
Peter Cong
Member
527 Points
681 Posts
Re: Why does my first ID suddently increase so big number?
Feb 19, 2013 01:39 AM|LINK
Hi, thanks for the help,
For my case, actually it is not the case, in my case, I have created about 15 rows of table, and deleted some 20 rows of records, so the total of rows are about less than 40 rows, since my table is just created and started to create some test data. However, my next ID increased to 1016 which is unbelievable, since I have never created 1000 records, and I checked my codes, there are no any loop to create the table rows.
I also check the SQL table from my SQL Management Studio, can not figure out how come the ID increased suddenly.
any idea?
Peter Cong
Member
527 Points
681 Posts
Re: Why does my first ID suddently increase so big number?
Feb 19, 2013 01:51 AM|LINK
From this post, it seems this is a bug of SQL server, look for more infor, thanks a lot for your help
Pbalan.in
Contributor
2144 Points
484 Posts
Re: Why does my first ID suddently increase so big number?
Feb 19, 2013 03:46 AM|LINK
Peter,
Identity column will be increased by 1 automatically, suppose if you add 4 records and the id become 4. If you delete the last record and again you add the new record the id will become 5 only not 4..
Peter Cong
Member
527 Points
681 Posts
Re: Why does my first ID suddently increase so big number?
Feb 19, 2013 12:54 PM|LINK
I understand that, if you read my post carefully, but for my case, the ID is not 5, the ID is suddenly increased to 1000, again, I just started to create the new SQL table and enter the records, considering some addition and deletions, it should not increase to 1000.
If you read somebody's posts in this thread, it seems this is the bug for SQL server db in VS2012. any idea?
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Why does my first ID suddently increase so big number?
Feb 19, 2013 02:46 PM|LINK
Peter,
It is a designed behavior based on the information from MS. Just know it and you may revist this issue after some long time with some service packs. It may or may not be fixed.
If you want to know more about this issue, you can check this (not a solution):
http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Peter Cong
Member
527 Points
681 Posts
Re: Why does my first ID suddently increase so big number?
Feb 21, 2013 12:52 AM|LINK
Thanks lot for your updates, it seems this is a common issue. it is OK if the application do not use this ID key, but for my case, my web apps is using as this key as a new transaction number generator instead of creating a new one.
For example, I have a product number field some thing like PROD0001, PROD0002 etc, it combines "PROD" + numbers which increased 1 by 1, so for my case, although the product number will have some gaps between each other, but for this case, the product number will be suddenly increased to a big number, users will ask why.
Any way, hope MS will have a fix pack soon, by the way, I am using VS2012, I think the SQL server database with VS2012 should be at least SQL 2008 RC, no sure.
cnranasinghe
Star
8885 Points
1798 Posts
Re: Why does my first ID suddently increase so big number?
Feb 21, 2013 02:56 AM|LINK
this how u can
DBCC CHECKIDENT (yourtablename, reseed, 5)
Peter Cong
Member
527 Points
681 Posts
Re: Why does my first ID suddently increase so big number?
Feb 21, 2013 03:18 AM|LINK
Hi , thanks a lot, I am new in SQL server, could you please give me the full codes of how to implement it?
So that means, I will add this codes in my C# code whenever I add a record to SQL table,, I have to reseed the ID data? Instead of using the automatically seeding from SQL database?