Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: auto increment problem

    hi
    I am facing problem in auto increment
    when i enter to commit the row if data is right then incerment is 1
    if enter data is wrong then error shows after correcting error
    it increments not by 1 as shows in picture

    does auto increment supports numeric(10)
    as
    id numeric(10) IDENTITY (1, 1),
    Attached Thumbnails Attached Thumbnails sqls.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you make an error while inserting, then that particular number is not re-used

    which should not matter to your app (if the gaps in the numbers are a concern, then you are doing something wrong)

    yes, you could use NUMERIC(10) for an IDENTITY column, but why would you?

    you can also use BIGINT like you are currently using, but unless you know you're going to have more than two billion rows, use INTEGER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    197

    Smile

    using numeric(10) is need of our project

    but just you see in figure when first i enter wrong string then error message come when I enter ok on that message then renter right string then
    just see on ID column which is primary key and auto increment but
    one time error occors one number scips as in id 17 to 19 not 18
    so whats the problem what to do to overcome
    Attached Thumbnails Attached Thumbnails sqs2.jpg  
    Last edited by ankur02018; 07-10-07 at 08:11.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no problem, and you don't need to do anything to overcome it

    gaps in IDENTITY values are irrelevant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This is simply how identity columns work in SQL Server (and Sybase for that matter). If you are looking to "overcome" this, you will have to create your own auto-increment functionality. As r937 said, if you can not stand the gaps in the sequence, the application is doing something wrong.

  6. #6
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by MCrowley
    This is simply how identity columns work in SQL Server (and Sybase for that matter). If you are looking to "overcome" this, you will have to create your own auto-increment functionality. As r937 said, if you can not stand the gaps in the sequence, the application is doing something wrong.
    so please can you give an example to create our own autoincrement
    how to create ???

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ankur, we advise against creating your own autoincrement

    why do you want no gaps in the numbers?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by ankur02018
    so please can you give an example to create our own autoincrement
    Seeing as I have never had the occasion or inclination to reinvent identity column functionality to support a bad design, no

  9. #9
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Quote Originally Posted by r937
    ankur, we advise against creating your own autoincrement

    why do you want no gaps in the numbers?

    because gaps in ID is primary key and gaps in numbers will make inconsistency
    so is there some simple way to avoid

    but

    if we run query explicitly we can fill those gaps

    but how can we do in gui mode

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ankur02108
    gaps in numbers will make inconsistency
    What will be inconsistant?
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    inconsistency?

    please give an example of where gaps would affect your application, and we will explain why it is a bad design choice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    many moons ago I programmed something with a logical delete process instead of real delete process because some auditor did not like gaps in ids provided by IDENTITY. Other than that, I can not think of any reason this should matter.
    “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.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's not even a good reason
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    people tell me to do stuff and I get a check every couple of weeks. I was younger.
    “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.

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by r937
    you can also use BIGINT like you are currently using, but unless you know you're going to have more than two billion rows, use INTEGER
    or more than 2 billion insert errors

Posting Permissions

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