1
I need to perform an alter table operation on several fields of a table and this table has more than one billion rows. One operation for each field takes approximately 2 hours and now imagines for several fields.
In development environment ran an operation for each field at a time and now to run a script that will affect all fields at once in Production will take more than 15 hours according to my estimate.
There is a way to optimize to run script faster?
This work is precisely to reduce the size of the table that is becoming unbearable.
Which sgbd do you use?
– Diego Schmidt
sql server 2012
– Leandro
You can use NOVALIDATE in ALTER TABLE that it will not validate anything and with that the instruction will run faster. Link Util
– Don't Panic
I think of something like: Create another table with the same structure, make the alter table without having any data in the table, insert the records in this table and rename with the original name.
– Diego Schmidt
@Everson, how would I use it? in the case of this example here: ALTER TABLE ALTER COLUMN Numeric VALUE(22,10)
– Leandro
In Management Studio, the table edition via Object explorer creates the script to do exactly as @Diegoschmidt said. Take the script and edit as needed.
– Alexandre Cavaloti