Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: remove IDENTITY in sql server

    suppose my table is


    create table TEST (
    id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)


    I want to remove IDENTITY(1,1) from id without dropping table

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi ankur
    I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
    http://www.dbforums.com/showthread.php?t=1620216
    It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.
    Last edited by pootle flump; 07-13-07 at 07:26.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Posts
    197

    Smile

    I am toatally new to sql server

    so

    initailly our project manager has ordered me to put auto incrment

    but

    now

    PM has said to remove auto increment

    I am in bit trouble dont know to remove auto increment

    so

    I asked help from you

    help me please

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    beh.

    just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why don't you want to drop the table?

    create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one

    something about this whole scenario does not smell right

    the project manager is actually you, isn't she
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Think bigger picture

    If you still need to retain the id, what is the PM's plan to manage new id's?

    And do you have a natural key?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2007
    Posts
    197

    Smile

    table is too large but I have taken a small example

    there large data in I want not to drop

    if there is any query are way to remove IDENTITY(1,1) without effecting
    data

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ankur02018
    table is too large but I have taken a small example

    there large data in I want not to drop

    if there is any query are way to remove IDENTITY(1,1) without effecting
    data
    No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...

    As a matter of fact, do you know how save a script in Enterprise Mangler?

    go create a test table with an identity column

    Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

    Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself

    Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old

    Again, how many rows are we talking about



    Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right....and then do


    SET IDENTITY_INSERT ON

    But that's a hack
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    And do you have a natural key?
    as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ankur02018
    I want to remove IDENTITY(1,1) from id without dropping table
    Dropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.

    The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.

    There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.

    -PatP

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?
    Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
    http://www.nigelrivett.net/SQLAdmin/...eProblems.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh I see!
    Thanks Poots

    I guess as long as you're aware of the potential problems you can use your best judgement per case.
    George
    Home | Blog

  15. #15
    Join Date
    Jun 2007
    Posts
    197
    Yes I got my answer to remove IDENTITY from column

    see attachment set IDENTITY to NO
    Attached Thumbnails Attached Thumbnails IDENTITY.JPG  

Posting Permissions

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