Primary Key upgrade to identity field worry 1

[ttdobj]

We have a table that has about 2,000,000 records in it. It has an ID field (integer) that is the Primary Key, but it does not auto-increment.

To insert a record you have to specify the new ID number. Which since we have multiple users inserting information with various applications, means I have to be very careful of PK clashes.

I am wondering whether it would be feasible to upgrade that field to auto-increment, make it an identity field. It would make the inserts a lot simpler, but I am concerned whether the table would survive intact.

There are 14 indexes on the table including a time field that is actually varchar!!

I have tried to upgrade the ID field on an old copy of the table in a backed-up database, but it could not re-create the time field index.

Should I just not bother with the idea of upgrading the ID field to auto-increment? Or should I pursue this further?

Any help would be really appreciated

Ta

John

 

[vbkris]

>>I have tried to upgrade the ID field on an old copy of the table in a backed-up database, but it could not re-create the time field index.

setting identity on a column does not disturb any other indexes as far as i know.

try this:

create the table as is.
finally add the identity to the id column...

Known is handfull, Unknown is worldfull

 

[ttdobj]

OK, I recreated the table in a new database with the old style of ID field.

Haven't added any records.
Then changed the ID field to identity.

I did all this in EM, and after 15 minutes I still have an Egg timer. I started profiler to look at the server whilst this is happening, too.

There have been a lot of:
declare @P1 int
set @P1 = 18150089
...
exec sp_cursoropen @p1 output, n'...

and

ALTER TABLE dbo.TestDB DROP CONTRAINT

and I'm now on CREATE NONCLUSTERED INDEX (index name)

It hasn't finished the indexes yet, I guess this will show if there is a problem with the database structure, or if it is a problem with the data itself ...

 

[ttdobj]

At just under 20 minutes Profiler reports that the change rolled back. Enterprise manager showed this message:

'TestDB' table
- Unable to create index 'Time'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

And just to confuse me completely, when I re-recreated the table in a test database, there wasn't any mention of the indexes created in the create table script.

If anyone has any ideas ...

 

[vbkris]

hmm, so when you are creating the table without the identity this error does not show up??? i am again confused as like i said, the auto-increment does not have anything to do with the index...

Known is handfull, Unknown is worldfull

 

[ttdobj]

I think the error with Time index was a seperate issue, as I re-re-recreated the table including the indexes. Changed the ID to identity, and on that occasion the change happened within seconds.

I think I might have to do some integrity checking on that backed up database!

Thanks for your words vbkris

 

[vbkris]

welcome...

Known is handfull, Unknown is worldfull