Results 1 to 20 of 20

Thread: How to reset ID of Database table

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    How to reset ID of Database table

    Dear Friend,

    I want to reset ID of Table database after deleted all rows. I want to make a button at Main form to reset Database table ID. Please help me

    I see many video and guide but they only make sql query and click execute.

    Thank you

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How to reset ID of Database table

    Why? there's not real reason to do so. Plus the only way I've found to reset it is to drop the table and re-create it.

    Even Identity Insert doesn't allow for a reset

    Code:
    create table MyTest (
    ID integer identity(1,1),
    Name nvarchar(50)
    )
    
    insert into MyTest (Name) values ('TechGnome')
    insert into MyTest (Name) values ('J_Group')
    insert into MyTest (Name) values ('Sitten')
    insert into MyTest (Name) values ('Shaggy')
    insert into MyTest (Name) values ('dday9')
    go
    
    print 'Striaght fresh insert'
    select * from MyTest
    
    delete from MyTest
    go
    
    print 'Turn on Identity_Insert on the table'
    set identity_insert MyTest on
    
    insert into MyTest (ID, Name) values (1, 'TechGnome')
    set identity_insert MyTest off
    
    print 'Identity Insert is off... resuming inserts'
    insert into MyTest (Name) values ('J_Group')
    insert into MyTest (Name) values ('Sitten')
    insert into MyTest (Name) values ('Shaggy')
    insert into MyTest (Name) values ('dday9')
    go
    
    select * from MyTest
    
    print 'Drop the table'
    drop table MyTest
    go
    
    print 'Re-create the table'
    create table MyTest (
    ID integer identity(1,1),
    Name nvarchar(50)
    )
    
    print 'New inserts'
    insert into MyTest (Name) values ('TechGnome')
    insert into MyTest (Name) values ('J_Group')
    insert into MyTest (Name) values ('Sitten')
    insert into MyTest (Name) values ('Shaggy')
    insert into MyTest (Name) values ('dday9')
    go
    
    select * from MyTest
    
    drop table MyTest
    go
    Produces the following output:
    Code:
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    Striaght fresh insert
    ID          Name
    ----------- --------------------------------------------------
    1           TechGnome
    2           J_Group
    3           Sitten
    4           Shaggy
    5           dday9
    
    (5 row(s) affected)
    
    (5 row(s) affected)
    
    Turn on Identity_Insert on the table
    
    (1 row(s) affected)
    Identity Insert is off... resuming inserts
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    ID          Name
    ----------- --------------------------------------------------
    1           TechGnome
    6           J_Group
    7           Sitten
    8           Shaggy
    9           dday9
    
    (5 row(s) affected)
    
    Drop the table
    Re-create the table
    New inserts
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    ID          Name
    ----------- --------------------------------------------------
    1           TechGnome
    2           J_Group
    3           Sitten
    4           Shaggy
    5           dday9
    
    (5 row(s) affected)
    You can see that the only way I got it to re-seed the id field was to drop it and re-create it.

    It looks like there is a DBCC CHECKIDENT https://msdn.microsoft.com/en-us/library/ms176057.aspx that will reset it... but it's an administrative call that could have side effects and shouldn't be called by a client system.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    Thank for reply,

    I understand you made SQL Query. But I dont know how to call SQL query to design code (sorry I am a beginer)

    Ex: I need make a button name "RESET ID" at form design and reset ID of table in database whenever click "RESET ID"

    How to do that?

    Thank you

  4. #4
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: How to reset ID of Database table

    Quote Originally Posted by J_Group View Post
    ...(sorry I am a beginer)

    ... and reset ID of table in database whenever click "RESET ID"

    ...
    Since you are a beginner, it is probably more important that you do not do this.

    Do you understand what it means to drop a table?
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: How to reset ID of Database table

    I'd like to know what you expect to accomplish by resetting the ID. After all, I see no good reason to do this and plenty of reasons not to. Therefore, since you claim to be a beginner, I suspect that there is a better way to achieve your goal than by resetting the ID. If you explain what the goal is, we could likely suggest an alternative.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    Attached is my program main design.

    I want to do :

    click scan : delete all information of table database
    Reset ID of Table database

    Name:  Untitled-1.jpg
Views: 1430
Size:  13.7 KB

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to reset ID of Database table

    So you want to TRUNCATE the table and reset the IDENTITY value back to start at 1 with the next insert - is that correct?

    Is this MS SQL server?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    I did it, Thank for your time

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: How to reset ID of Database table

    People have been telling you this is probably a bad idea and asked why you think you should take this approach. You have not answered their question. It's going to be hard to help you, if your not willing to answer questions.

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to reset ID of Database table

    Quote Originally Posted by techgnome View Post
    It looks like there is a DBCC CHECKIDENT https://msdn.microsoft.com/en-us/library/ms176057.aspx that will reset it... but it's an administrative call that could have side effects and shouldn't be called by a client system.

    -tg
    It does not require admin - just that you be the owner of the schema

    Caller must own the schema that contains the table, or...
    and if the goal is to have a RESET button on a form that TRUNCATE's and reset's the identity value it's fine for the client app to use.

    It's actually suggested in the description of what it is used for

    You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.
    I didn't see much about warnings about it's use.

    I can see issues in a large multi-user shared environment with a casual button like this - issues that just call for some level of control.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    I was Use Trigger Event when delete. So I done! Thank for your help

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to reset ID of Database table

    Quote Originally Posted by J_Group View Post
    I was Use Trigger Event when delete. So I done! Thank for your help
    Must be a bit of a language barrier here as what you just said makes little sense and certainly does not seem related to a RESET of an ID.

    Did you mean UPDATE of an ID - as in another record?

    If that was the case we certainly went off in a very different direction in regard to answers!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    below is TRIGGER I was made on table database

    Code:
    CREATE TRIGGER [Trigger]
    	ON [dbo].[info]
    	FOR DELETE
    	AS
    	BEGIN
    		DBCC CHECKIDENT ('dbo.info', RESEED, 0)
    	END

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to reset ID of Database table

    Why would deleting one record cause you to reset the whole tables id?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    Sir,

    My software need empty table before read systems information and insert to database.

  16. #16
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: How to reset ID of Database table

    Let's take a step back, and take a deep breath.

    You have answered, many times, what you want to do. What you aren't answering is why you would want to do it. The reason so many people are asking is not because they are picky, but because this is a very strange thing to do, and you may think you need to do it but not actually need to do it.

    Usually you design a table to have at least one column where the value for every row is unique. We call this a "primary key", and it's important to how the database optimizes itself. Sometimes, the primary key is a column that you want to provide, like a customer ID number. Other times, you don't have a value in your data that makes sense for a primary key. In those cases, an auto-incrementing number makes sense.

    Because that column isn't really part of your data, and only exists to help you distinguish between certain rows, it's very rare that you care what the specific value of the autoincrementing number is. Even if the table is empty, most applications don't care if the number starts at 0, 100, 10,000, or any other number. So without any other context, trying to reset the auto-incrementing number is a waste of time. Nothing in most programs cares about what the next number will be. And often, when code does care about the number, it has many mistakes.

    So we want to hear why you think you need to reset the ID. What are you doing with the ID that cares about its value? It might be a bad idea, and we could help you get around it. Or, it could turn out that it doesn't matter, and if we decide that we can help you not waste your time.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  17. #17
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: How to reset ID of Database table

    From your screenshot, I don't think you should delete the table ID at all. You showed a single record with some hardware information in it. There are two possibilities here. The first is that there is ONLY a single record, in which case you REALLY don't want the ID field to be auto-increment, because that causes the trouble that caused you to write this post. When I want a certain record to have a certain ID (which is often the case for tables that have only one record), I just remove the auto-increment so that I can set the ID to whatever I want it to be.

    The other alternative is that there are multiple records and you want the primary keys to have some meaning. Sometimes it is necessary or useful for the primary key field to have meaning, but there's a school of thought that holds that the primary key should NEVER hold any meaning and should be just a key. In such a case, resetting has no value whatsoever. You appear to be using some kind of hybrid, where you want the primary key to have meaning AND to have specific values. How about just creating a primary key field that is an autoincrement, and which you don't care about, and adding the ID field as just an integer field. You could put in whatever data you wanted it to have and wouldn't have to worry about it being incremented or resetting it.
    My usual boring signature: Nothing

  18. #18
    PowerPoster SJWhiteley's Avatar
    Join Date
    Feb 2009
    Location
    South of the Mason-Dixon Line
    Posts
    2,256

    Re: How to reset ID of Database table

    Quote Originally Posted by szlamany View Post
    ...

    I didn't see much about warnings about it's use.

    I can see issues in a large multi-user shared environment with a casual button like this - issues that just call for some level of control.
    I'm not sure documentation on a command to do something like this would provide a 'warning' per se: the warning is inherently stated 'it resets the ID'. Seems like its the same as resetting your car odometer. It's how it's used which determines the level of 'warning' that might be required.

    I think most people are questioning why you'd want to do it, because we consider all the systems we have worked on and doing such a thing would be a big Bad Idea (TM).

    I'll also add that you are one of the better experts on databases, so are in a better position to recommend or question what can be done vs. what should be done.
    Last edited by SJWhiteley; Nov 3rd, 2015 at 02:25 PM.
    "Ok, my response to that is pending a Google search" - Bucky Katt.
    "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
    "Before you can 'think outside the box' you need to understand where the box is."

  19. #19

    Thread Starter
    New Member
    Join Date
    Oct 2015
    Posts
    15

    Re: How to reset ID of Database table

    Sir,

    I just start learn VB by myself around 1 week. So I really dont understand much.

    I need ID to update exactly device at other window.

    like this
    Name:  Untitled-1.jpg
Views: 1476
Size:  24.4 KB

  20. #20
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: How to reset ID of Database table

    In that case, you don't need to reset the ID at all. All you need is for the second form to know what the ID is. If that second form is intended to update something in the table, the ID simply identifies the item that needs to be updated. Therefore, the ID shouldn't ever change, but it doesn't matter what the ID actually is, so resetting the ID isn't necessary.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width