November 22, 2021 at 2:34 pm
Hi,
We have restored the production backup to a different machine for testing purposes. We found that one of our tables which has auto-increment column marked as "identity" is having different values when compared to Production for the same set of records in the same table.
For example, In Production we have a table T1 having columns: id (auto-increment) and tran_id (foreign key references a primary key) .
After restoring the DB to Test Server, We find the same table T1 has different id values for their corresponding tran_id values as compared to that of in Production.
Is this normal behavior of SQL Server RESTORE? Please advise
Thanks a lot
November 22, 2021 at 2:58 pm
Yes, absolutely. The test server has no idea what identity values are being assigned in prod and vice versa.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 22, 2021 at 3:16 pm
Do you mean just a simple backup and restore, not data changes at all, no inserts, deletes, nothing like that, and suddenly SOME values are different after the restore?
Nope.
That doesn't happen. Not ever. Something else has to be going on. You're restoring a different database. You are modifying data in the second server. Something else is happening.
Even in the event of a corrupt restore, corruption doesn't softly change a few data values, but otherwise leaves everything intact. It doesn't work that way at all. Instead, it will just break some part of the database, or even the whole thing.
As Scott says, if you're adding data in Production and in this non-production server, yeah, different amount of rows being added in different orders at different times will result in differences in the identity column values. Not a shock at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2021 at 4:31 pm
An identity will increment when an attempt to insert occurs. If the insert fails, the identity will still increment.
When SQL is restarted, identities increment by 1000. So, if the last identity is 1000, and you re-start SQL, the next identity would be 2001. Is that possibly what you are seeing?
If you are depending up an identity to always be unique and have no gaps in the sequence, that is not how they work.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2021 at 3:24 pm
...is that actually a thing? restarting SQL Server increments identity fields by 1000? I've never seen that and it doesn't sound like a thing? Maybe I'm not understanding the context?
December 9, 2021 at 3:37 pm
...is that actually a thing? restarting SQL Server increments identity fields by 1000? I've never seen that and it doesn't sound like a thing? Maybe I'm not understanding the context?
Yes, it's a "thing". SQL writes to disk every 1000 identities generated.
If you are relying on identities to be sequential and unique, then they likely are not.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2021 at 3:41 pm
So, if I created a test table right now, and made an identity column, added 10 rows to it, then rebooted the server, and added another row, you're saying the next value would be 1011?
December 9, 2021 at 3:42 pm
This was removed by the editor as SPAM
December 9, 2021 at 3:46 pm
So, if I created a test table right now, and made an identity column, added 10 rows to it, then rebooted the server, and added another row, you're saying the next value would be 1011?
Yep. Try it
https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/
The question is do you consider this to be a problem, and if so, why?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2021 at 3:48 pm
Not really, the point is for them to be unique.. so; it does this with ints, what about GUIDs?
Also, cool, learned me a thing to day. Thanks
December 9, 2021 at 3:55 pm
Not really, the point is for them to be unique.. so; it does this with ints, what about GUIDs?
Identities are not unique. I can insert the value "1000", as an example, into an identity column as many times as the table can take them.
Again, if you are depending upon an identity to be unique and sequential, don't. There is nothing about an identity that enforces this. You need to know, and understand these things, and take the proper steps if that is what your requirements are.
What about a GUID? They are not sequential. And, technically, they may not be unique.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2021 at 4:58 pm
I suppose the only time I've ever really USED identity column I also identified it as the PK. I guess I'm not really sure why else you would use an identity field if its just going to contain an arbitrary incremental number that is non unique. Anyways, I've hijacked this thread enough I think ;). Thanks.
December 9, 2021 at 5:07 pm
I suppose the only time I've ever really USED identity column I also identified it as the PK. I guess I'm not really sure why else you would use an identity field if its just going to contain an arbitrary incremental number that is non unique. Anyways, I've hijacked this thread enough I think ;). Thanks.
Ahhhh. One of the worst generic practices that too many people seem to do.
Create an identity column, default it to the clustered primary key!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2021 at 5:29 pm
oogibah wrote:So, if I created a test table right now, and made an identity column, added 10 rows to it, then rebooted the server, and added another row, you're saying the next value would be 1011?
Yep. Try it
https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/
The question is do you consider this to be a problem, and if so, why?
I would think the new number would be 1001 (rather than 1011). Reasoning: The first time you need an identity value, SQL sets up the internal controls for that identity to cover the next 1000 (by default) numbers, which in this case would be 1 to 1000. Thus, the last number used up is 1000, so the next 1000 numbers generated -- in this case when the server restarted -- would be 1001 to 2000.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 9, 2021 at 6:12 pm
oogibah wrote:I suppose the only time I've ever really USED identity column I also identified it as the PK. I guess I'm not really sure why else you would use an identity field if its just going to contain an arbitrary incremental number that is non unique. Anyways, I've hijacked this thread enough I think ;). Thanks.
Ahhhh. One of the worst generic practices that too many people seem to do.
Create an identity column, default it to the clustered primary key!
I figured you might mention that, but most the stuff I've created is both not very wide and would only be filtered on nvarchar values. Because of this the identity column was only being used for joining to other tables and to prevent the table from just being a heap.
Michael L John wrote:oogibah wrote:So, if I created a test table right now, and made an identity column, added 10 rows to it, then rebooted the server, and added another row, you're saying the next value would be 1011?
Yep. Try it
https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/
The question is do you consider this to be a problem, and if so, why?
I would think the new number would be 1001 (rather than 1011). Reasoning: The first time you need an identity value, SQL sets up the internal controls for that identity to cover the next 1000 (by default) numbers, which in this case would be 1 to 1000. Thus, the last number used up is 1000, so the next 1000 numbers generated -- in this case when the server restarted -- would be 1001 to 2000.
Makes sense, but I still can't wrap my head around why you would want a column that contains an arbitrary incremental, albeit not.. consistent number that also isn't unique.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply