Hello,
I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.
Basically I have a webpage that when I create a new item it populates a new ID, which is the primary key and read only when I go into the database backend so i cannot manually change this.
When I have been doing some testing adding fields to the table and deleting this has obviously given me new ID's such as 113, 114 etc. But on my live site I need these ID's to be sequential, so on my live site the last ID is 108, but now when I add another item the ID is 115 because the number between this and 108 have been taken up.
So my question is really do I need to shrink my database or files to take this down to 109, if so is database shrink or file the best to do? Also i have had someone add a new live item and this has been assigned 113, would I need to delete this, shrink and then re-enter?
Any suggestions would be greatley appreciated.
Many thanks.
shrinking a database does not effect IDENTITY values, you need to look at the following
DBCC CHECKIDENT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
you could also look at running a SET IDENTITY INSERT ON;
and then updating the ID values manually to your desired values.
Derek
|||Thank you very much for you reply. Guess I need to run a query on something like the follwoing from the link you sent:
USE Assets;
GO
DBCC CHECKIDENT ('Assets.ID', RESEED, 30);
GO
But how do you specific which row to replace?
Also how does the SET IDENTITY INSERT ON command work, sorry quite new to SQL.
|||the easiest way for you to do this (assuming you have some form of consistency to your IDs) would be to run the following code:
SET IDENTITY_INSERT dbo.Table ON
GO
UPDATE dbo.Table SET ID = ID -30 --this is the consistency I am referring to, if your IDs are all over the place it going to not be fun lol
GO
SET IDENTITY_INSERT dbo.Table OFF
|||You cannot use IDENTITY column if you want to ensure no gaps in the values. For example, if a particular transaction rolls back an insert then the generated identity value will be consumed and a subsequent insert will get the next higher value. If you want guaranteed sequential numbers generation then you need to do it using a sequence table yourself.
Please take a look at the link below for an implementation that shows how to use sequence table.
http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx
|||Thank you for your help I have now sorted the problem. Much appreciated.