March 29, 2011 at 2:16 pm
Ok I have the following table...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Applications](
[ApplicationsID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationName] [nvarchar](100) NOT NULL,
CONSTRAINT [pk_ApplicationID] PRIMARY KEY CLUSTERED
(
[ApplicationsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
As soon as that code creates my table I run the following..(part of unit tests)
delete table Applications
dbcc checkident('Applications', reseed, 0)
and the message is this:
Checking identity information: current identity value 'NULL', current column value '0'.
Unfortunately this means that the first row I insert get's ApplicationID of 0.
I cannot use truncate as applicationID is a foreign key in another table so I'm stuck with delete.
Is there another way to set the next Identity value?
Donalith
March 29, 2011 at 2:29 pm
if you reseed to 0 then the id value will be 0 , what do you want the Id value to be?
also what are you trying to do with this code?
delete table Applications
March 29, 2011 at 2:38 pm
I think this is probably along the lines of what you're looking for. You don't need to reseed to 0, it can be any value you want:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('applications') is not null
drop table applications
CREATE TABLE [dbo].[Applications](
[ApplicationsID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationName] [nvarchar](100) NOT NULL,
CONSTRAINT [pk_ApplicationID] PRIMARY KEY CLUSTERED
(
[ApplicationsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
delete Applications
dbcc checkident('Applications', reseed, 1)
I changed the delete line to what I think you're trying to do.
March 29, 2011 at 3:26 pm
If you delete Applications and reseed the Identity to 0 when the Identity's initial value is null you will get a 0 for the first Identity BUT
If you delete Applications and reseed the Identity to 0 when the Identity's initial value is NOT null you will get a 1 for the first Identity.
Donalith
March 29, 2011 at 3:27 pm
Mr. or Mrs. 500,
I cannot drop the table because the applicationID is a foreign key in another table. I'm stuck with delete unless I want to mess around with dropping constraints all over the place.
Since it's for unit testing I just went with SET IDENTITY_INSERT table ON so I would have specific numbers to run against.
Thanks,
Donalith
March 29, 2011 at 7:53 pm
I do not seem to have the same result for
INSERT INTO [dbo].[Applications]
SELECT 'some name here or there'
DELETE FROM [dbo].[Applications]
dbcc checkident('Applications', reseed, 0)
INSERT INTO [dbo].[Applications]
SELECT 'more of this nonsense some name here or there'
SELECT * FROM [dbo].[Applications]
Result: Note the ApplicationsID is NOT 0
ApplicationsIDApplicationName
1 more of this nonsense some name here or there
March 29, 2011 at 8:09 pm
Perhaps it has something to do with the fact that the create table statement is being run against the database from an external .NET application.
When you run the create table statement and then run the dbcc statement against it before inserting anything do you get a null value seed?
I also note that you ran the dbcc checkident statement after you inserted something. At that point running the delete and/or the dbcc statement is going to give you a non-null value and the increment and SEED portion will function normally.
Donalith
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply