Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    17

    Question Unanswered: Remove Autoincrement

    Hi,

    i have a field in a table that is autoincrement, i need to alter table and remove autoincrement from this field, what should i do ?

    Alter Table myTable Alter Column myColumn bigint NOT NULL ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure you're using Microsoft SQL Server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Presumably you are referring to an IDENTITY column. There is no way to remove the IDENTITY property from an existing column. You have to create a new column or create a new table.

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    we can't remove the identity column for exsting column.

    once try like this
    right click on table --> design--> columnproperties-->identity specification

    then keep no option then ur identityproperty will disable
    for new insertion of records u can give the required value

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    bklr, just to be clear. The process you described actually creates a new table, populates it, drops the old table and then renames the new one.

    This is one way to create a new non-IDENTITY column but not necessarily the best way because it involves copying all the data and locking the entire table for the duration. That could be very expensive for large tables.

    Also, bugs have been reported with the scripts produced by the design tool in Management Studio. I don't recommend that method for production systems. Better to create the script, test it and only then run the script on the target system.

  6. #6
    Join Date
    Nov 2008
    Posts
    17
    ok, the best way i found is create a non identity column copy data from identity column to new created one , then drop the identity column.

    was coool !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •