Do I need to shrink my database?

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.