identity problem

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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