Alter Table

• mkg_1232000

  Old Hand

  Points: 307

  More actions

  July 3, 2002 at 2:28 am

  #357567

  Hi

  I have a table with identity column. I want to remove the identity on this column. Is it possible with alter table command. From the design table option using enterprise manager, I could disable this property but could not find any trace how it did from the profiler. Any clue ?

  Thx

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  July 3, 2002 at 8:07 am

  #432046

  Enterprise Manager goes through the following steps:

  1) Creates a new table without the IDENTITY property on the column

  2) Copies the data to the new table via an INSERT statement

  3) Drops the table with the IDENTITY property

  4) Renames the new table to the old table's name

  K. Brian Kelley

  bkelley@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/bkelley/

  K. Brian Kelley
  @kbriankelley

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  July 3, 2002 at 10:05 am

  #432056

  Brian's method is the only way. You cannot remove an identity column.

  Steve Jones

  sjones@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/sjones