Changing column type from bigint to numeric (18,0) in sql server
I have about 10 tables that have data in them. I need to change fields with bigint datatype to numeric (18,0).
We have checked the data in our DB, there would be no data loss. In our lower environment, we did the following:
- Took a backup of an existing table, temporarily rename it
- Create a new table with a numeric data type
- Populating data from the backup table
- If everything is ok, delete the backup table
The above process that we followed in the environments below .
But we cannot follow the above procedure when it comes to prod. We would like to change using the ALTER statement. Since this is a PROD environment, we must be careful with the changes. As I said earlier, there would be no data loss.
But I still wanted to know - what happens inside when we execute the instruction ALTER
?
Will it open the table and recreate it with new definitions and return the data? If so, is there a risk associated with this?
Any thoughts on how this might be handled correctly in PROD would be appreciated.
source to share
I could suggest an approach that does not recover data. Use a calculated column instead. Something like that:
sp_rename 'table.dbo.col', '_col', 'COLUMN';
alter table table add col as (cast(_col as numeric(18, 0));
Then you can access col
as the type you want. You won't have to rewrite any data, so there won't be any locks or other performance issues. It select *
will certainly be a little overkill, but you probably shouldn't.
source to share