May 16, 2003 at 8:20 am
Hi all
I have a problem with a table with an IDENTITY column. In order to illustrate my problem I have created a small table according to:
CREATE TABLE [dbo].[W_Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL ,
[Age] [int] NULL ,
[OwnerID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[W_Person] WITH NOCHECK ADD
CONSTRAINT [PK_W_Person] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
When I create two new records, the field ID is assigned the next available value.
So far everything works fine.
THE PROBLEM:
If I insert a record with the following SQL statements:
SET IDENTITY_INSERT W_Person ON
INSERT INTO W_Person (ID, Name, Age, OwnerID)
VALUES (8000, 'John Johnson', 30, 2)
SET IDENTITY_INSERT W_Person OFF
and then deletes the same record. As a next step I tries to insert a new record and let SQL Server generate a new id, the new id is assigned the value of 8001 instead of 3.
Please, can anybody help me on this one!
//Peter
May 16, 2003 at 9:04 am
This behavior is by design.
SQL Server will start the identity seed so that the next value to get inserted into the table will be after the records inserted with IDENTITY_INSERT turned on. The reasoning is so that you don't come back around and get a conflict later on.
Even if you were to reseed with DBCC CHECKIDENT(), if you specify a seed value that is less than the maximum value, you'll get an error.
Is there a reason you need to insert the record as a specific integer value? In cases like this, the use of the IDENTITY column causes a lot of headaches, as you've already run across. If I remember right, Antares has posted some great solutions in the past to solving this dilemma without the use of IDENTITY. You might try and search back through the forums if he doesn't see this first.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 16, 2003 at 9:26 am
Hi,
that's right, I think you only get 1,2,3 when you drop the identity column and create it again. But in most cases you can't do that. If you need an ongoing numbering you have to find a workaround as Brian mentioned.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 16, 2003 at 9:38 am
Thanks!
DBCC seems to do the trick for the moment. I'am using two tables. one table that stores the data permanently and one thats used for loading and unloading data from the first table. the ID's are generated in the "temporary" table illustrated by W_person.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply