Discovered the other day that reseeding in SQL Server gets different results based on whether the table has ever had data in it. This is regardless of whether all the data in the table has been deleted or not. Copy the following SQL code into Query Analyzer and see for your self...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WeirdIdentityReseedProblem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WeirdIdentityReseedProblem]
GO
CREATE TABLE [dbo].[WeirdIdentityReseedProblem] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[AnotherField] [varchar] (255) NULL
) ON [PRIMARY]
GO
--#2 Now just execute here on down...
DELETE FROM WeirdIdentityReseedProblem
DBCC CHECKIDENT ('[dbo].[WeirdIdentityReseedProblem]' , RESEED, 0)
insert into WeirdIdentityReseedProblem (AnotherField) values('FirstValue')
insert into WeirdIdentityReseedProblem (AnotherField) values('SecondValue')
insert into WeirdIdentityReseedProblem (AnotherField) values('ThirdValue')
--select * from WeirdIdentityReseedProblem
DECLARE @FirstId INT
select @FirstId = Id from WeirdIdentityReseedProblem where AnotherField = 'FirstValue'
Print 'Value of Id of first record: ' + CAST(@FirstId AS VARCHAR(30))
You'll always get 0 as the reseed value. Executing from #2 on down will always return 1.
Replace #2 on down with...
IF EXISTS (SELECT 1 FROM WeirdIdentityReseedProblem)
BEGIN
DBCC CHECKIDENT ('[dbo].[WeirdIdentityReseedProblem]' , RESEED, 0)
END
DELETE FROM WeirdIdentityReseedProblem
insert into WeirdIdentityReseedProblem (AnotherField) values('FirstValue')
insert into WeirdIdentityReseedProblem (AnotherField) values('SecondValue')
insert into WeirdIdentityReseedProblem (AnotherField) values('ThirdValue')
--select * from WeirdIdentityReseedProblem
DECLARE @FirstId INT
select @FirstId = Id from WeirdIdentityReseedProblem where AnotherField = 'FirstValue'
Print 'Value of Id of first record: ' + CAST(@FirstId AS VARCHAR(30))
Now whether you execute the whole text or from #2 on down you get 1. Unfortunately, if the table is empty, it doesn't work. Since we practice TDD at Thycotic, we always reset the database before each unit test. If our unit test leaves the table empty we're hosed. FYI- the first portion of the SQL would be in a different file and is for demonstration only.
Plug the following just after #2 and this will break it...
DELETE FROM WeirdIdentityReseedProblem
Anybody got any suggestions?