January 14, 2009 at 2:00 pm
Hello, I have an identity problem.
I'm designing a new data model. I have two scripts, one to create all the tables and another that load some initial data into the tables. I have identity(1,1) on some tables and expect to have these identities start at 1. My load script starts by deleting and dbcc checkinit reseed to 0 where there are identities. For the most part, the load script works and I can make my script do my bidding.
But sometimes it goofs up and instead of starting identities at 1, I get 0 as the first row. This happens when I recreate all the tables. I have created a sample script to show the problem.
My server version is: Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Aug 5 2008 12:31:12 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1).
What's going on here? Am I doing something wrong? Is this a known problem with my server version?
In the code below, I get 0 on the first insert and 1 for the second insert.
If I drop the first reseed, I get 1 on both inserts.
SELECT @@VERSION
GO
IF OBJECT_ID ('id_test') IS NOT NULL DROP TABLE id_test
SET ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TABLE id_test (
my_id INT IDENTITY(1,1) NOT NULL,
my_DESC varchar(80) NULL
)
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 0)
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 0)
INSERT INTO id_test ( my_DESC) SELECT 'dummy 2'
select * FROM id_test
DELETE FROM id_test
go
January 14, 2009 at 2:11 pm
Instead of deleting and re-seeing, have you thought about using truncate?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 2:18 pm
Truncate will not work if he has FKs/constraints. However have you tried setting the reseed to 1? Per BOL:
the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
So it makes sense that it uses zero if no rows exist and you recreated the table.
January 14, 2009 at 2:31 pm
raymond.lew (1/14/2009)
Hello, I have an identity problem.
Who doesn't every once in a while 😉
Maybe this helps you figure out what's going (wr)on(g).
SELECT @@VERSION
GO
IF OBJECT_ID ('id_test') IS NOT NULL DROP TABLE id_test
SET ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TABLE id_test (
my_id INT IDENTITY(1,1) NOT NULL,
my_DESC varchar(80) NULL
)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 0)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 1)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
INSERT INTO id_test ( my_DESC) SELECT 'dummy 2'
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
truncate table id_test
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
DBCC CHECKIDENT(id_test, RESEED)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED,0)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test
INSERT INTO id_test ( my_DESC) SELECT 'dummy 2'
select * FROM id_test
DELETE FROM id_test
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2009 at 3:20 pm
Thank for the pointers, dmc and Gsquared:
The answer is that's just the way it is. On dmc's reply I went to read the manual:
If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
So for my purposes:
truncate is better than delete/reseed because it is more consistent. Thanks Gsquared.
It's counter intuitive that delete/reseed is not always the same as truncate.
January 14, 2009 at 4:54 pm
Thanks to all. I'm finally back to where I started, which is to delete and reseed. As dmc says, you can't truncate if you have foreign keys and I have lotsa FK.
I'm not sure why I am writting here, as I'm FK'd and I have an identity problem; I really should go see a shrink.
I have re-hashed my script to reseed conditional on a local script variable. I looked over ALZDBA's code, but I could not find a DB value that tells me how SS is going to act.
Thanks again. Your help is appreciated. It's good to get the support so that I can stop banging my head on the wall.
January 16, 2009 at 1:02 am
What I intended to show is that sqlserver handles it diferently if it as no table content data.
After create it states
Checking identity information: current identity value 'NULL', current column value 'NULL'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.
vs
Checking identity information: current identity value 'NULL', current column value '0'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.
vs
Checking identity information: current identity value '0', current column value '0'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.
IF OBJECT_ID ('id_test') IS NOT NULL DROP TABLE id_test
SET ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
set nocount on
go
CREATE TABLE id_test (
my_id INT IDENTITY(1,1) NOT NULL,
my_DESC varchar(80) NULL
)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_01
DBCC CHECKIDENT(id_test, RESEED)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_02
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 0)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_03
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_04
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 0)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_05
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_06
select * FROM id_test
DELETE FROM id_test
DBCC CHECKIDENT(id_test, RESEED)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_07
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_08
select * FROM id_test
/* */
go
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1x'
go 10
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_09
select * FROM id_test
DELETE FROM id_test
/* */
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED, 0)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_10
INSERT INTO id_test ( my_DESC) SELECT 'dummy 2'
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_11
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
truncate table id_test
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_12
DBCC CHECKIDENT(id_test, RESEED, 0 )
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_13
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
select * FROM id_test
DELETE FROM id_test
------------------------------------------- the same statements
DBCC CHECKIDENT(id_test, RESEED,0)
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_14
INSERT INTO id_test ( my_DESC) SELECT 'dummy 2'
select * FROM id_test
DELETE FROM id_test
Select IDENT_CURRENT( 'id_test' ) as current_Ident_Id_test_15
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2009 at 6:23 am
So you stated that you couldn't user truncate because you have some FK's. If this is a script, that you're repopulating a database with, I'd imagine that your FK's wouldn't change very often...
Why not just disable them. You can turn them off 1 by 1 with ALTER TABLE tablname NOCHECK CONSTRAINT command?
Or if you want to turn them all off, check http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm. Look at scenario 4.
You could theoretically, do the above, use msforeachtable to truncate all of your tables, turn your constraints back on and load your data...
Simple easy script with the additional benefits of when you add a new table or FK you don't have to update it.
-Luke.
January 16, 2009 at 9:57 am
2 ALZDBA
Thank you for your script. I am still disappointed that this is consistently inconsistent with what I expect. Using my visual parsing powers, I can see that if current identity value is 'NULL' then the next id is the current column value, otherwise next is current column value + 1. I don't think I should be writing code to handle stupid situations like this. Consistency is good in a database and it is consistent on this <8-0
Here's my script
IF OBJECT_ID ('id_test') IS NOT NULL DROP TABLE id_test
SET ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
set nocount on
GO
DECLARE @id_inserted VARCHAR(10)
CREATE TABLE id_test (
my_id INT IDENTITY(1,1) NOT NULL,
my_DESC varchar(80) NULL)
PRINT '-- here we have 3 tests with 2 key statements (1 reseed with zero (2 insert 1 row'
PRINT '-- and I would like to see a consistent value for my identity column with all tests'
PRINT '-- the question is can I game SS by using checkident and what nots?'
PRINT '-- 1 - just created + reseed and insert'
PRINT '-- 2 - after delete + reseed and insert'
PRINT '-- 3 - truncate tab + reseed and insert'
PRINT 'they all should be like the default of create table id(1,1) with first row id being 1'
PRINT ' or least that''s what I want AND I don''t really care about anyone else'
PRINT '-- 1 - just created'
DBCC CHECKIDENT(id_test)
PRINT '-- 1 - rseeding'
DBCC CHECKIDENT(id_test, RESEED, 0)
PRINT '-- 1 -- after reseeding'
DBCC CHECKIDENT(id_test)
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
SELECT @id_inserted = CAST(my_id AS VARCHAR) FROM id_test
PRINT '-- 1 id just inserted '+ @id_inserted
PRINT '-- 1 -- after 1 insert'
DBCC CHECKIDENT(id_test)
DELETE FROM id_test
PRINT '-- 2 -- after delete'
DBCC CHECKIDENT(id_test)
PRINT '-- 2 -- next is reseeding'
DBCC CHECKIDENT(id_test, RESEED, 0)
PRINT '-- 2 -- after reseed after insert'
DBCC CHECKIDENT(id_test)
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
SELECT @id_inserted = CAST(my_id AS VARCHAR) FROM id_test
PRINT '-- 2 id just inserted '+ @id_inserted
truncate TABLE id_test
PRINT '-- 3 -- after truncate'
DBCC CHECKIDENT(id_test)
PRINT '-- 2 -- next is reseeding-- afte truncate '
DBCC CHECKIDENT(id_test, RESEED, 0)
INSERT INTO id_test ( my_DESC) SELECT 'dummy 1'
SELECT @id_inserted = CAST(my_id AS VARCHAR) FROM id_test
PRINT '-- 3 id just inserted '+ @id_inserted
2 Luke L
I have decided to simply get a count on a table. If not count of 0, delete and reseed. Hacking the FK's would be okay but too much work. Thanks for the link to the script. I like collecting them. Almost like a free lunch.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply