Timeline for Changing primary key from IDENTITY to being persisted Computed column using COALESCE
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 21, 2016 at 3:05 | history | bounty ended | Mr Moose | ||
| Nov 21, 2016 at 3:05 | vote | accept | Mr Moose | ||
| Nov 18, 2016 at 8:46 | comment | added | Mr Moose | I think your suggestion of using IDENTITY_INSERT (with a high seed value for existing apps) will work well. Aaron Bertrand provided an answer here with a good little example on testing it with concurrency. We've modified our data load tool to be able to handle tables that need to specify identity values and we'll get onto some further testing in the coming weeks. | |
| Nov 15, 2016 at 4:06 | comment | added | Solomon Rutzky |
@MrMoose That's fine. But as far as I can tell, there is no reason that IDENTITY_INSERT would pose a problem. Definitely need to test it before ruling it out.
|
|
| Nov 15, 2016 at 3:19 | comment | added | Mr Moose | As you suggested, change 1 made little difference. The ID we will use will be allocated outside of the current database and used to relate records. It may well be that my understanding of sessions isn't quite right so IDENTITY_INSERT might work. It will take a little time for me to investigate that though, so I won't be able to report back for a little while. Thanks again for the input. It is much appreciated. | |
| Nov 15, 2016 at 2:50 | comment | added | Solomon Rutzky |
@MrMoose Actually, regarding "IDENTITY_INSERT can only be used for one table per session", what exactly is the issue here? 1) you can only insert into one table at a time, so you turn it off for TableA before inserting into TableB, and 2) I just tested and contrary to what I had thought, there are no concurrency issues -- I was able to have IDENTITY_INSERT ON for the same table in two sessions and was inserting into both with no problems.
|
|
| Nov 15, 2016 at 2:39 | comment | added | Solomon Rutzky |
@MrMoose Yeah, I updated my answer to include more info about Sequences at the end. It wouldn't work in your situation anyway. And I was wondering about potential concurrency issues with IDENTITY_INSERT, but haven't tested it. Not sure option #1 is going to solve your overall issue, it was just an observation to reduce needless complexity. Still, if you have multiple threads inserting new "external" IDs, how do you guarantee that they are unique?
|
|
| Nov 15, 2016 at 2:33 | comment | added | Mr Moose | Thanks so much for your answer. You raise a few points that were discussed here internally. Unfortunately, some of these won't work for us for a couple of reasons. Our database is quite old and somewhat brittle and runs under 2005 compatibility mode so SEQUENCES are out. Our app data push occurs via a data load tool that obtains new records from service broker queues and pushes them via multiple threads. IDENTITY_INSERT can only be used for one table per session, and the current thinking is our architecture can't cater for that without significant change. I'm testing your fist suggestion now. | |
| Nov 15, 2016 at 2:25 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
No SCOPE_IDENTITY for Sequences :-(
|
| Nov 14, 2016 at 23:35 | history | answered | Solomon Rutzky | CC BY-SA 3.0 |