Bug regarding identity columns?

I'm seeing some weird behavior regarding identity columns in MSSQL 2000.

In a specific client database we have this table:

ID Name SecLevDG Flags
-- -- -- --
1029528 xxx 0 0
1029529 xxx 0 0
1049676 xxx 0 0

While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are

alter table Authority drop constraint apkAuthorityId
alter table Authority drop column SecLevDg
alter table Authority add new_id integer identity

This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:

ID Name Flags new_id
-- - -- --
1029528 xxx 0 16777220
1029529 xxx 0 16777220
1049676 xxx 0 16777220

Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?

DBCC CHECKIDENT returns:
Checking identity information: current identity value '1', current column value '1'.

DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included):
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
[...]
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).

Regards,
Oskar Berggren

WHen I'm creating a new identity column, I use this:

columnName int IDENTITY (1,1)

Perhaps it is balking because you aren't providing a seed value?

Or, if there are consistency errors, there's an issue with the table itself...

|||Also, although I can not find it now, I seem to remember reading that 2005 likes things to be specifically defined as NULL or NOT NULL.|||

I encountered an issue like this in SQL Server 2005 RTM (I think SP1 fixed it). I was able to work around it by setting Max Degree of Parallelism (MAXDOP) to 1 for the transaction and then back to 0 afterwards.

My theory on this is that multiple processors are working to set the identity and the processors somehow end up with the same value. By having it done with a single processor it might run a bit slower but it resolved the issue for me.

I've never seen this issue in 2000, though. I assume you have the latest service packs?

Regards,

Jared