Post reply

IDENTITY crisis in SQL Server 2005, RTM version

  • November 24, 2005 at 8:14 am

    #166074

    Hello,

    Has anyone else experienced IDENTITY problems in the RTM version of SQL Server 2005?

    I have a nice 26 column table with 2.367.315 rows.

    I want to add an identity column and I do this:

    ALTER TABLE myTable ADD myIDColumn INT NOT NULL UNIQUE IDENTITY(1,1)

    ...and all is well. Until I get this result:

    Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.myTable' and index name 'UQ__myTable__3C69FB99'. The duplicate key value is (5).
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    So I bang myself in the head thinking that somehow I have managed to execute my ALTER TABLE in a way not approved by SQL Server. So I hit F5 again hoping for better luck. And YES, this time I got a different result:

    Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.myTable' and index name 'UQ__mYTable__3D5E1FD2'. The duplicate key value is (16).
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    ...and what you sharp eyed guys out there has already seen is that the duplicate key is 16 and not 5 as last time! Running it again:

    Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.myTable' and index name 'UQ__myTable__3E52440B'. The duplicate key value is (13).
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    It is interesting to note that the conflicting ID is always in the low range, considering the 2M+ rows in my table. So I thought maybe there will be major number of IDENTITY conflicts in my table. Well, only one way to find out! So I cut out my UNIQUE clause and rerun my query (with success this time) and run a

    SELECT

    myIDColumn

    FROM

    myTable

    GROUP BY myIDColumn

    HAVING

    COUNT(*) > 1

     

    But here the interesting result is just four rows! These ones were the result:

    3

    10

    2

    29

    So here I go, SELECTing the first 100.000 rows into a new table, adding my UNIQUE Identity column to test if this behaviour always happens.

    But no. This table lets me add my UNIQUE IDENTITY column without any problems.

    And by now you would think I would have given up? Well, no. Not yet! I INSERT INTO myTable_Test SELECTing * FROM myTable.

    I.e my test table now contains 100.000 + 2.367.315 = 2.467.315 rows.

    I add my column with UNIQUE IDENTITY and after 2 minutes of impatiantly waiting for an response:

    Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.CallUMTS_2005_11_01d_A_Side_Test' and index name 'UQ__CallUMTS_2005_11__3F466844'. The duplicate key value is (15).
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    ...And now I give up for a while. If someone could enlighten me I would be more then pleased!

    Regards, Hanslindgren

  • November 24, 2005 at 10:25 am

    #605733

    Seems like a bug. Have you tried a full select into to add the identity?

    I know it would only be a workaround but if that gets you out of the jam!!

  • November 25, 2005 at 3:40 am

    #605817

    Now I have tried that and it works. Thanks.

    Still remains my interesting conundrum if this really is a bug.

  • November 25, 2005 at 3:41 am

    #605818

    ...and a test to see if it is just this data, this table structure and this hardware and software configuration that produces this effect.

  • November 25, 2005 at 9:40 am

    #605877

    Did you run dbcc checktable against the table? does it have a PK?

     

  • November 28, 2005 at 4:23 am

    #606035

    Yes, here is the result:

    DBCC results for 'myTable'.
    There are 2367315 rows in 27765 pages for object "myTable".
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    And no, it has no primary keys, no constraints whatsoever and no indexes.

  • November 28, 2005 at 7:11 am

    #606067

    I did the same thing as you to a table with 185

    ,380,901 rows, it completed with no errors. The table has a PK with clusered index.

    I guess that your database is upgraded from SQL 2000. Since dbcc checktable gives no error, it sounds to me the upgrading has some problem in handling heaps. I'll do more test to confirm this.

     

  • November 28, 2005 at 7:16 am

    #606071

    Well, the database has NOT been upgraded from SQL Server 2000. Newly created and a new table has been BULK INSERTed into.

    Coul you try again without your PK and indexes.

     

  • November 28, 2005 at 9:10 am

    #606137

    Also worthy of noting is the fact that I have SELECTed * INTO TestTable2 from myTable.

    Adding my UNIQUE IDENTITY column failed also for this new table.

    I guess one of my future trials should be to use other data and/or changing the number of columns and try again...

  • November 29, 2005 at 7:43 am

    #606412

    I tried without PK and index in two ways:

    1) remove PK and index in sql 2000, backup and restore to sk5, add the identity and unique key

    2) remove the pk and index in 2k5, add the identity and unique key 

    Both are succeeded.

    Do you have nvarchar(max), ntext, image data type column in the table?

     

  • November 29, 2005 at 7:44 am

    #606413

    Forget to ask, what edition you are using?

     

  • November 29, 2005 at 9:35 am

    #606447

    Not sure if this is related, but I experienced the same error when trying to creae a unique clustered index in Management Studio. The error complaint about duplicate values, even though all values in the column were unique. I repeated it twice with the same error. I when wrote a create index statement and that executed without error.

    I when dropped the index again and recreated it with the GUI. And yes this time it worked. Haven't had the same problem snce, but it seems that there might be a bug in SSMS.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • November 29, 2005 at 11:14 am

    #606494

    is one of those unique values a null?

    Cheers,

    david russell

  • November 30, 2005 at 2:01 am

    #606633

    Well, I at least, by adding a column defined as an IDENTITY NOT NULL, expect the column to contain NO nulls. Both by the constraint NOT NULL and by the properties of IDENTITY that is supposed to generate an increasing unique sequence. I.e Not Null.

    Regards,

    HansLindgren

  • December 1, 2005 at 8:01 am

    #607066

    I tried the bulk insert scenario, no problem.

     

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply