Post reply

Check Identity-Specification- 'No'

  • April 6, 2008 at 11:19 pm

    #184814

    Can anybody tell how to change the identity-Specification to 'No' , through Query.

  • April 6, 2008 at 11:42 pm

    #798949

    If it's temporary... (from Books Online)

    SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

    If it's permanent removal you want, you'll need to define a sister table, copy the data to it, drop the original table, rename the sister table to the original table name, reapply indexes and grants, and reapply keys...

    You could also add a column to the original table, update it with the IDENTITY column value, and then drop the IDENTITY column... you'll still need to mess with the PK and foregin keys.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 6, 2008 at 11:49 pm

    #798950

    I do not believe that you can do this with an ALTER TABLE.

    The SOP for this kind of table change is to create a new table with its characteristics changed per your requirements (including dropping the Identifier), copy all of the data in, drop the old table and rename the new one to match to old name.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply