Results 1 to 28 of 28

Thread: SQL Server not increasing by 1 but by 100

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    SQL Server not increasing by 1 but by 100

    Sometimes, when I create a new record in a table which has its indentity set to increase by 1, it increases it by 100 as in this graphic.
    What can be the source of this error?
    Thanks
    PK

    Name:  SqlServer error 01.jpg
Views: 268
Size:  17.3 KB

  2. #2
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: SQL Server not increasing by 1 but by 100

    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: SQL Server not increasing by 1 but by 100

    Does SQL-Server has a mechanism as SQLite for AUTOINCREMENT-Columns?
    In SQLite, if you have an Integer-PK-Column with AUTOINCREMENT set to True, a once used Value will never be used again.
    Say, you inserted Rows 1 to 1000, and then deleted Rows 800-1000 ---> Next Insert gets Value 1001

    But dee-u's link is actually very interesting
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: SQL Server not increasing by 1 but by 100

    Yes, dee-u has the right solution for Sql Server 2017 to set IDENTITY_CACHE = OFF, but it is not available in Sql Server 2012 with which I work.
    Zvoni, I am aware of losing deleted records ID's.

    PK

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: SQL Server not increasing by 1 but by 100

    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: SQL Server not increasing by 1 but by 100

    It was quite an interesting excursion.
    There are two solutions that Zvoni's link gives.
    This one is even more detailed for newbies:

    https://www.codeproject.com/Tips/668...-Value-Jump-Is

    PK

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by Zvoni View Post
    Does SQL-Server has a mechanism as SQLite for AUTOINCREMENT-Columns?
    In SQLite, if you have an Integer-PK-Column with AUTOINCREMENT set to True, a once used Value will never be used again.
    Say, you inserted Rows 1 to 1000, and then deleted Rows 800-1000 ---> Next Insert gets Value 1001


    But dee-u's link is actually very interesting
    Crashing aside under normal circumstances MS SQL will not reuse identity columns. Here is a little test you can run:

    create table #TestIt(MyIdentity int identity(1,1),MyColumn Varchar(5))
    -- load five rows
    insert into #TestIt(MyColumn) Values('1')
    insert into #TestIt(MyColumn) Values('2')
    insert into #TestIt(MyColumn) Values('3')
    insert into #TestIt(MyColumn) Values('4')
    insert into #TestIt(MyColumn) Values('5')
    -- look at them
    select * from #Testit
    -- delete last two inserted
    Delete from #TestIt where MyIdentity in (4,5)
    -- reinsert
    insert into #TestIt(MyColumn) Values('4')
    insert into #TestIt(MyColumn) Values('5')
    -- look at them - the identity is not reused.
    select * from #Testit

    drop table #TestIt
    Attached Images Attached Images  
    Please remember next time...elections matter!

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: SQL Server not increasing by 1 but by 100

    Tyson,
    that's the reason why i've stopped using AUTOINCREMENT in SQLite.
    Theoretically (!!!), you could get into the situation, that you have a table, which has been filled up with 2,147,483,648 entries
    (Highest value for 32-Bit signed integer, as AFAIK SQLite uses signed INTEGER),
    all prior 2,147,483,647 entries have been deleted,
    and with the next INSERT the table replies with "No, Sir. Table is full! --> ***? There is only one record inside the table"
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server not increasing by 1 but by 100

    Personally I would say "why do you care?" Identity columns are meant to be underlying, invisible, control values; a surrogate key and nothing more. The DBMS needs to manage them but they should require no interaction from you at all.

    I'm going to guess that your reason for caring falls into one of two categories:-

    1. You're a little bit OCD? Me too, by the way. There's something deeply offensive about gaps in sequences and it grates at me very soul whenever I see one. None the less, it's important to realise that this is my (and possibly your) issue, not SQL Server's. The best advice here is: just get over it. It has precisely zero functional impact on your software.

    2. You're using the value for something you shouldn't, e.g. exposing it in the UI, using it to infer the order of records etc. If this is what you're doing, stop doing it! For a variety of reasons, ranging from security to ongoing maintainability, it's a really bad practice (I can't really tell you why without knowing the exact purpose you're using it for but if you let me know I'll happily fill that gap). If you need a value to fulfil some function beyond the need for a surrogate key you should be generating an appropriate value deliberately and intelligently in your code.


    @Zvoni, unfortunately overflowing the datatype is a risk regardless of whether the intermediate values have been deleted or not. It helps to think of the ID column as recording how many records have ever existed rather than how many exist now and select a datatype (e.g. a BigInt) accordingly. If you're number of operations is going to exceed your datatype and you don't want to increase the datatype size for some reason, then you need to get a strategy in place to re-index the table (and all it's children) when that happens. There're very good reasons why SqlServer (or any other major database, to my knowledge) doesn't "backfill" identity columns - chiefly that trying to avoid collisions becomes a frickin' nightmare.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: SQL Server not increasing by 1 but by 100

    You didn't by some change do in insert with INDETITY_INSERT On then insert into the ID column and at the end do IDENTITY_INSERT OFF did you? If so the identity will have been reset to the last value you inserted and pickup from there
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: SQL Server not increasing by 1 but by 100

    FunkyDexter,

    Well .. what can I say?
    In the first instance I wish to thank you for your excellent contribition. I have learned a lot.
    And ... some introspection says I have OCD. That can be the only reason in terms of that you set out
    But ... when I got the problem I thought it was my own ignorance again which is normal practise this side.
    I was quite relieved to find out it happens in th best of families.

    Thanks
    PK

  12. #12

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: SQL Server not increasing by 1 but by 100

    GaryMazzone,
    No, it was not the case and I am too ignorant to do such a thing.
    PK

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by FunkyDexter View Post
    Personally I would say "why do you care?" Identity columns are meant to be underlying, invisible, control values; a surrogate key and nothing more. The DBMS needs to manage them but they should require no interaction from you at all.

    I'm going to guess that your reason for caring falls into one of two categories:-

    1. You're a little bit OCD? Me too, by the way. There's something deeply offensive about gaps in sequences and it grates at me very soul whenever I see one. None the less, it's important to realise that this is my (and possibly your) issue, not SQL Server's. The best advice here is: just get over it. It has precisely zero functional impact on your software.

    2. You're using the value for something you shouldn't, e.g. exposing it in the UI, using it to infer the order of records etc. If this is what you're doing, stop doing it! For a variety of reasons, ranging from security to ongoing maintainability, it's a really bad practice (I can't really tell you why without knowing the exact purpose you're using it for but if you let me know I'll happily fill that gap). If you need a value to fulfil some function beyond the need for a surrogate key you should be generating an appropriate value deliberately and intelligently in your code.


    @Zvoni, unfortunately overflowing the datatype is a risk regardless of whether the intermediate values have been deleted or not. It helps to think of the ID column as recording how many records have ever existed rather than how many exist now and select a datatype (e.g. a BigInt) accordingly. If you're number of operations is going to exceed your datatype and you don't want to increase the datatype size for some reason, then you need to get a strategy in place to re-index the table (and all it's children) when that happens. There're very good reasons why SqlServer (or any other major database, to my knowledge) doesn't "backfill" identity columns - chiefly that trying to avoid collisions becomes a frickin' nightmare.
    Funky,
    i want to amend my Statement: You're right, of course!
    In SQLite i don't use AUTOINCREMENT on tables which either are
    1) a solitairy table (PK is not pointing to a FK anywhere)
    or
    2) if a PK points to a FK (or more) which has/have the full cascading constraints
    If for whatever reason i don't/cannot use the cascading constraints i use AUTOINCREMENT

    And you're right with the major-DB-Players.
    I've seen commercial Software on MySQL that doesn't use a single FK-Constraint (everything done through code by Frontend. Don't ask! I've seen the setup in Workbench).
    Now that's an re-indexing nightmare
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server not increasing by 1 but by 100

    some introspection says I have OCD
    We're programmers. It goes with the territory. I can certainly understand why seeing that phenomenon would give one pause for thought, though and it's always better to ask than brush these things under the carpet.

    In SQLite i don't use AUTOINCREMENT on tables which either are
    1) a solitairy table (PK is not pointing to a FK anywhere)
    I agree whole heartedly with that but would add one slight nuance. Make sure you have a clustered index on the table somewhere, probably backing up some sort of primary key. Otherwise the table will be treated as a "heap" which is a nightmare for the DB Engine to find records in because it means the records are completely unordered on the B-Tree and every single access will involve a full table scan, even if it would otherwise be supported by a non-clustered index.

    Personally, I'd probably be looking to set up a natural key with a clustered index or introduce a surrogate key of no natural key presented itself. Access patterns might change that though.

    I've seen commercial Software on MySQL that doesn't use a single FK-Constraint
    Ugh, me too. I've had to work on more than one system like that.
    Last edited by FunkyDexter; Feb 11th, 2020 at 04:02 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by FunkyDexter View Post

    I agree whole heartedly with that but would add one slight nuance. Make sure you have a clustered index on the table somewhere, probably backing up some sort of primary key. Otherwise the table will be treated as a "heap" which is a nightmare for the DB Engine to find records in because it means the records are completely unordered on the B-Tree and every single access will involve a full table scan, even if it would otherwise be supported by a non-clustered index.

    Personally, I'd probably be looking to set up a natural key with a clustered index or introduce a surrogate key of no natural key presented itself. Access patterns might change that though.
    To avoid misunderstandings (AFAIK this pertains only to SQLite): On such tables i don't use AUTOINCREMENT, but i do use PrimaryKey-Columns the classic way (Integer-Column, PrimaryKey, NonNull, Unique - but no Autoincrement).
    SQLite has something called ROWID, meaning: even if i have an INTEGER-Primary-Key-Column with no AUTOINCREMENT, i don't have to set that Column during INSERT's. SQLite is still incrementing that value by itself, using the value from ROWID (which you could see as something akin to a "shadow-PK"). And yes, i do have indexes on such primary keys
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server not increasing by 1 but by 100

    On such tables i don't use AUTOINCREMENT, but i do use PrimaryKey-Columns the classic way
    Yeah, that's exactly pattern I use by default. I might perhaps favour a natural key over a surrogate integer but it's very situational. The important bit is just having a primary key.

    SQLite has something called ROWID
    That's interesting and sounds useful. I'll have a google about that I think. Thanks for the info.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by FunkyDexter View Post
    That's interesting and sounds useful. I'll have a google about that I think. Thanks for the info.
    There you go:
    https://www.sqlite.org/rowidtable.html
    https://www.sqlite.org/autoinc.html
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: SQL Server not increasing by 1 but by 100

    I wish to respond to the idea of the FK, although it may be a new thread, but I do not really to open one.
    I am at a loss if you talk about FK's. Of course I join one table on a field of a second table which has a reference to the PK of the first table and which may be regarded as a FK, but I thought that ordinarily joining them like FROM table1 LEFT JOIN table2 on ... is all that is required.
    What is the use of a FK and how does the indexing use it - and how can I index it? If you do not mind. I could do this in Access, but not on Sql Server.
    Thanks
    PK

  19. #19
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server not increasing by 1 but by 100

    I wish to respond to the idea of the FK, although it may be a new thread, but I do not really to open one.
    Another way to say that is I know what the rules are but I'm making an exception for me
    Please remember next time...elections matter!

  20. #20
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: SQL Server not increasing by 1 but by 100

    Think of an FK (foreign Key) as it must reside in the parent table before it can reside here (i.e. Let say you have a row in the parent table with and ID of 1000 (as the primary key in you customer table) now you want to add an order to the system for that customer the data you want to add to the field custermId in the Order table needs to exist in Customer before it can be added in the Order table)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  21. #21
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server not increasing by 1 but by 100

    Meh, we'll bend the rules where it suits. Particularly if it's the OP bending the rules. It's his thread after all. And Zvoni and I have done a good job derailing it already...

    That said, Peekay, you may want to consider that piggy backing one thread onto another (even your own) may not get the best help for you. You're likely to get less eyes on your second question because 1. It's not described by the subject of the thread and 2. People are likely to read the first few posts and not spot that the thread has changed direction.

    is all that is required
    It's all that's required but it may not be all that's desirable. For example, it won't prevent you from entering a record in a child table that refers to a parent that doesn't exist. Or deleting a parent record that still has children. Enforcing that sort of data integrity matters because, for example, orders that don't belong to a customer tend to upset the accounting department. And Towns that aren't in a State make it really hard for the Sales Director to dish out the annual bonus to his team. If you don't enforce the foreign keys in your database you make it REALLY easy for that stuff to get out of whack. Even if you have all the nice defensive code in your application you're not protected from a Database Admin making a mistake when manipulating the data directly in the back end (which happens ALOT more than we like to admit).

    It also has advantages to performance though these are harder to quantify. The Optimiser is often able to build more efficient query plans because it knows that certain relationships exist. E.g. it will know that a record in a child table has a corresponding record in the Parent so, if that's relevant, it doesn't need to check.

    And it's also REALLY useful when some poor sad-sack like me inherits your app (which I bet there's no documentation for 'cause all us developers know deep down that documentation is for losers, no matter what we say to the project manager), it gives us a fighting chance of working out how the data all hangs together.

    I would encourage everyone to enforce Foreign Keys in the database. If nothing else it's an added level of robustness to your application and it's easy to do. In SQL Server, for example, you simply right click on the Keys node of your table in the explorer, Select New Foreign Key and tell it what the relevant columns are.
    Last edited by FunkyDexter; Feb 11th, 2020 at 10:51 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  22. #22
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by FunkyDexter View Post
    Meh, we'll bend the rules where it suits. Particularly if it's the OP bending the rules. It's his thread after all. And Zvoni and I have done a good job derailing it already...

    That said, Peekay, you may want to consider that piggy backing one thread onto another (even your own) may not get the best help for you. You're likely to get less eyes on your second question because 1. It's not described by the subject of the thread and 2. People are likely to read the first few posts and not spot that the thread has changed direction.

    It's all that's required but it may not be all that's desirable. For example, it won't prevent you from entering a record in a child table that refers to a parent that doesn't exist. Or deleting a parent record that still has children. Enforcing that sort of data integrity matters because, for example, orders that don't belong to a customer tend to upset the accounting department. And Towns that aren't in a State make it really hard for the Sales Director to dish out the annual bonus to his team. If you don't enforce the foreign keys in your database you make it REALLY easy for that stuff to get out of whack. Even if you have all the nice defensive code in your application you're not protected from a Database Admin making a mistake when manipulating the data directly in the back end (which happens ALOT more than we like to admit).

    It also has advantages to performance though these are harder to quantify. The Optimiser is often able to build more efficient query plans because it knows that certain relationships exist. E.g. it will know that a record in a child table has a corresponding record in the Parent so, if that's relevant, it doesn't need to check.

    And it's also REALLY useful when some poor sad-sack like me inherits your app (which I bet there's no documentation for 'cause all us developers know deep down that documentation is for losers, no matter what we say to the project manager), it gives us a fighting chance of working out how the data all hangs together.

    I would encourage everyone to enforce Foreign Keys in the database. If nothing else it's an added level of robustness to your application and it's easy to do. In SQL Server, for example, you simply right click on the Keys node of your table in the explorer, Select New Foreign Key and tell it what the relevant columns are.
    Don't they have sarcasm in that obscure body?
    Please remember next time...elections matter!

  23. #23

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: SQL Server not increasing by 1 but by 100

    TysonLPrice,
    I will respect your view and open a new thread. I just did it here not to lose the valuable inputs of Zvoni, FunkyDexter and others and I am sure they will participate in the new thread.
    I listened in admiration to their view on keys and I am the ignoramus they refer to - which I do not want to remain. I revere most of these learned guys to act as my bridge over troubled waters.

  24. #24
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server not increasing by 1 but by 100

    Don't they have sarcasm in that obscure body?
    Oh yeah. Shooooore they do.

    Erm, I'm not sure we deserve to be revered. But, yeah, foreign keys are a good thing.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  25. #25
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by Peekay View Post
    TysonLPrice,
    I will respect your view and open a new thread. I just did it here not to lose the valuable inputs of Zvoni, FunkyDexter and others and I am sure they will participate in the new thread.
    I listened in admiration to their view on keys and I am the ignoramus they refer to - which I do not want to remain. I revere most of these learned guys to act as my bridge over troubled waters.
    Now I feel bad
    Please remember next time...elections matter!

  26. #26
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL Server not increasing by 1 but by 100

    Quote Originally Posted by Zvoni View Post
    that's the reason why i've stopped using AUTOINCREMENT in SQLite.
    Theoretically (!!!), you could get into the situation, that you have a table, which has been filled up with 2,147,483,648 entries
    (Highest value for 32-Bit signed integer, as AFAIK SQLite uses signed INTEGER),
    all prior 2,147,483,647 entries have been deleted,
    and with the next INSERT the table replies with "No, Sir. Table is full! --> ***? There is only one record inside the table"
    SQLite is using signed-integers, but those are "dynamic ones" and go up to (signed) 64Bit.

    Dynamic, because at "DB-Page-Level" they are efficiently stored in a special, efficient format,
    which requires basically only the "needed Bytes, according to the current bit-ness".

    In any case, your fear of "Err-messages" due to "Integer-Overflow in the Auto- or RowID-Fields",
    is unfounded.

    Here the two different Auto-ID-modes of SQLite...

    First case (and how I use the feature usually).
    Code:
      With New_c.Connection(, DBCreateInMemory)
        .Execute "Create Table T(ID Integer Primary Key, Txt Text)"
        
        .Execute "Insert Into T(ID,Txt) Values(2147483647,'x')" 'we enforce a higher value
        .Execute "Delete From T" 'but here we delete the just added Record again
        
        'The effect in this case (ID Integer Primary Key) is, that SQLite will:
        '"fall back to the last max-ID-value it can find in the table"
        '(which in this case is a NULL, so the following inserts will start with ID 1 again)
        .Execute "Insert Into T(Txt) Values('a')"
        .Execute "Insert Into T(Txt) Values('b')"
        .Execute "Insert Into T(Txt) Values('c')"
        
        With .OpenRecordset("Select * From T")
          Debug.Print !ID, !Txt: .MoveNext
          Debug.Print !ID, !Txt: .MoveNext
          Debug.Print !ID, !Txt
        End With
      End With
    The above prints out for the remaining 3 records (after deletion of the priorily "enforced high-value"):
    Code:
     1            a
     2            b
     3            c
    And here the second case (extending the Auto-ID-Def with the Keyword AutoIncrement):
    Code:
     With New_c.Connection(, DBCreateInMemory)
        .Execute "Create Table T(ID Integer Primary Key AutoIncrement, Txt Text)"
        
        .Execute "Insert Into T(ID,Txt) Values(2147483647,'x')" 'we enforce a higher value
        .Execute "Delete From T" 'but here we delete the just added Record again
        
        'The effect in this case (ID Integer Primary Key AutoIncrement) is, that SQLite will:
        '"not search for anything to fall back to", instead the last *seen* max-ID-value
        'will be used as the base for further increments on the Auto-ID-Field"
        .Execute "Insert Into T(Txt) Values('a')"
        .Execute "Insert Into T(Txt) Values('b')"
        .Execute "Insert Into T(Txt) Values('c')"
        
        With .OpenRecordset("Select * From T")
          Debug.Print !ID, !Txt: .MoveNext
          Debug.Print !ID, !Txt: .MoveNext
          Debug.Print !ID, !Txt
        End With
      End With
    Printout in this case is now the following:
    Code:
     2147483648   a
     2147483649   b
     2147483650   c
    ...also showing, that "going over the 2^31 boundary for 32bit signed Ints", will be handled without any problems
    (needs a quite a lot more inserts over the next 10000 years or so, until one's reaching the 2^63 limit of that ID-field).

    HTH

    Olaf

  27. #27
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: SQL Server not increasing by 1 but by 100

    If this is a unique identity then I''m currious on what will happen if you use DBCC CHECKIDENT(N'#Youtablehere', RESEED, 3); or better yet DBCC CHECKIDENT(N'#Youtablehere', RESEED, 1);
    Can you try that?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  28. #28
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server not increasing by 1 but by 100

    That will start the sequence again from 1. It's handy if your next Identity has advanced beyond the value of the last one inserted but not if you've got a "gap". It will allow you to fill the gap but then it will start trying to use the same ids as records you already have on the far side of the gap.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

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