July 27, 2018 at 11:07 pm
There is also another way, unless you "must" keep the column in the same position. Add a new column with a different name with the same data type marking it as null. Once the column is added you can update the column with the data from the original column, even doing it in batches to reduce contention. Once all the data is moved and verified, you can drop the original column, rename the new column to the old column name, make it not null if needed and adding back any constraints, indexes, defaults, etc.
Of course, it you are relying on creation order in any queries, procedures, views, etc., this will cause problems there. But then hopefully no one is doing that, right?
July 27, 2018 at 11:32 pm
Fair enough, Lynn. I edited my post to say "and preserve column order." I knew that was possible but it did not occur to me to offer that up. Thanks for raising it.
I definitely think it's an advanced technique. It would take some engineering work to use a new column as you described and there is some risk that clients could access the table in an unintended middle state if not implemented appropriately. I likely would not opt into it unless I was pressed to minimize the time the table was offline as much as possible but I could see it being the right card to play in certain situations.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 30, 2018 at 4:20 am
Orlando Colamatteo - Friday, July 27, 2018 10:21 PM@Nigel, I noticed that too but they probably arrived here through a search engine which means others will too...which means I am compelled to provide some additional info.
Fair point 🙂
July 31, 2018 at 5:09 am
I think best way to do this is with partitioning ALTER TABLE ... SWITCH command. It is instant, can be done within a tran if you like. See Dan Guzman's add-or-remove-identity-property-from-an-existing-column-efficiently. You really don't need to know anything about partitioning to use this.
July 31, 2018 at 11:56 am
Ooh, that's a handy thing to know. Partition switching, even on "non partitioned" tables is one of those underused tricks as it is, but I never realized it could be used to get rid of identity columns before.
September 6, 2018 at 2:11 am
nigel. - Friday, July 27, 2018 2:26 AMHI,
FYI, in case you hadn't noticed, you are replying to a 7 year old post 🙂
🙂
yes I know that.
But the solution provided there is also not given in previous comments.
may be this will help to someone to resolve there issue in future while searching for solution on forums. 🙂
and if The solution provided is wrong then someone will provide me the correct solution.
September 6, 2018 at 5:21 am
Mike Good - Tuesday, July 31, 2018 5:09 AMI think best way to do this is with partitioning ALTER TABLE ... SWITCH command. It is instant, can be done within a tran if you like. See Dan Guzman's add-or-remove-identity-property-from-an-existing-column-efficiently. You really don't need to know anything about partitioning to use this.
This is going into the toolkit. Thanks for sharing it.
Now if Microsoft would adjust the code generated by the SSMS Table Designer when Script or Save is clicked in this scenario they’d really be raising their level.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply