Identity seed issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Identity seed issue

Author

Topic

mailmechandru
Starting Member

3 Posts

Posted - 2008-04-04 : 11:23:11

Hi ..

I have an issue here. I create a DB and some tables through a script in SQL Server. I have lot of tables in DB and quite a lot have identity columns with seed set to 1 and increment set to 1. The scripts executed fine and all the tables created. Now when I do the first insert records into the tables the identity column associated starts with 0 even though the seed is set at 1. Its the case with all the tables where the identity column are set. The first records into all these tables starts with 0 for all the identity columns.

I could'nt figure out what is causing this issue ..

Any fix for the issue ..

thanx

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 11:24:42

Check what IDENT_SEED() for some of these tables return?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mailmechandru
Starting Member

3 Posts

Posted - 2008-04-08 : 09:25:48

In response to your reply .. the following is the script which is used to create a table on the new DB.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACH_AccountSetUp]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ACH_AccountSetUp](
[ACHAccountId] [bigint] IDENTITY(1,1) NOT NULL,
[EntityId] [int] NOT NULL,
[EntityTypeId] [tinyint] NOT NULL,
[NameOnAccount] [nvarchar](50) NOT NULL,
[BankAccountNumber] [nvarchar](24) NOT NULL,
[ABA] [nvarchar](9) NULL,
[BankName] [nvarchar](60) NULL,
[Comment] [nvarchar](200) NULL,
[IsActive] [tinyint] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedUserId] [int] NOT NULL,
CONSTRAINT [PK_PAPAccount] PRIMARY KEY CLUSTERED
(
[ACHAccountId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
------------------------
table is created with the above script
----------------
select table_name, IDENT_SEED('ACH_AccountSetUp') as 'SEED'
from information_schema.tables order by 1
-----------------
now if i run the above sql I get 1 as the seed which is correct
-------------------------------------
DBCC checkident ('ACH_AccountSetUp',Noreseed)
---------------------------
now if I execute the above command, a NULL is returned.
I am expecting 1 to be returned here instead of NULL.
----------------------------
So now if an insert is made into this table the identity seed starts with 0.
Even if I reset the seed by the following command ..
DBCC checkident ('ACH_AccountSetUp',RESEED,1)
the identity column does not start with 1.
--------------------------------------------

Any idea why this is happening ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources