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
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
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.
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
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
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
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
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
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.
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
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
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
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
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
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
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)
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
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.
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.
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.
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).
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?
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