Removing and Adding IDENTITY on columns in a table

  • Thread starter Thread starter Nevyn Twyll
  • Start date Start date
N

Nevyn Twyll

From C#, using ADO, or through direct SQL, how would I Alter an existing
column to add or remove Identity from it?

How does Enterprise Manager do it?

- Nevyn
 
EM does it <something> like this (but you could check profiler to be sure)

1) create a new table of the same schema less the identity property on the
column
2) load the new table with the data of the old table
3) drop the old table
4) rename the new table to be that of the old table
5) tack on any indexes

Like I said, it could be in a different order. This is a common topic.
Other MVP or all around knowledgable posters would know the exact skinny.
Perhaps a google search would turn up something better than my post.

hth
Eric
 
From C#, using ADO, or through direct SQL, how would I Alter an existing
column to add or remove Identity from it?
Click to expand...

One path would be:

(a) create a new table
(b) copy the data over
(c) add the identity property if it wasn't there before and you're adding
it; leave as is, without the identity property, if it was there before and
you're dropping it.
(d) drop the old table (you might have to drop relationships and other
dependencies)
(e) rename the new table to the old name
(f) don't forget to re-create primary key, indexes, constraints, triggers,
etc.

As you can imagine, for larger tables, this will be quite prohibitive if you
expect to perform the task synchronously from C#. If this is a web app, it
is doubtful the browser will succeed without timing out.
How does Enterprise Manager do it?
Click to expand...

Profiler will tell you exactly how, but I can tell you this, it's not a
simple statement (at the very least it will make a complete copy of the
table).

Realistically now, how many times are you going to be dropping and adding
the IDENTITY property on existing columns in a table?
 
Back
Top