Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: IDENTITY resetting identity columns?

    Hey guys,

    I've been looking around and it seems the way to reset an identity column is to do:
    Code:
    DBCC CHECKIDENT('MyTable', RESEED)
    My question is... Is there a simpler way to do it? I.e. even if I delete my database and recreate it, the identity columns still seem to take off from where they left

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    truncate table will do it also.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I generally find that if you care what the values are, there may be a problem in your design or thinking.

    and what can be simpler than that?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dbguyfh
    even if I delete my database and recreate it, the identity columns still seem to take off from where they left
    Uh? Are you certain about that?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Yes... unless I'm not deleting it properly. I right-click on the db in the manager studio, then choose "Delete." When the dialog comes up, I check the box "Close existing connections"

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    NAUGHTY! Use code, not the GUI, where possible.
    Code:
    DROP DATABASE myDB
    Sounds to me that you are deleting the connection rather than the database. I'll need to check coz I never drop dbs with the GUI
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - what you describe is how to drop a db via the GUI. No idea then. I've checked profiler and it submits DROP DATABASE myDb. Don't know what is happening to you....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by dbguyfh
    Hey guys,

    I've been looking around and it seems the way to reset an identity column is to do:
    Code:
    DBCC CHECKIDENT('MyTable', RESEED)
    My question is... Is there a simpler way to do it? I.e. even if I delete my database and recreate it, the identity columns still seem to take off from where they left
    How is deleting and recreating a database easier than a DBCC command?

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    "DROP DATABASE myDb" didn't work for me... It just froze, I think because my database was in use. When I use the GUI, I can choose "Close existing connections"

    Cascred: I just thought DROP DATABASE would be easier since I wouldnt have to run DBCC for each and every table

Posting Permissions

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