adding a not null column to a large existing table
hi everyone
i'm trying to optimize a very simple, yet fairly long running script.
i have a large table that i need to add a new column with a not null and default value.
the sql is simple:
this runs for more than 25 minutes as the table that it's adding to has over 70 columns and > 100,000 rows.
i tried various things like wrapping the alter within a transaction, also adding the column first as a nullable column, updating it with a default value using tablockx and holdlock, and then adding the not null constraint on the column.
any input/solution?
thanks in advance.
update:
the problem was resolved and the slow down isolated to the transaction logging file growing and causing so many i/o reads that the query became long running. truncating the transaction log to a decent size and setting the file growth at a reasonable percentage optimized the query to under 8 minutes for half a million rows. (!)
i'm trying to optimize a very simple, yet fairly long running script.
i have a large table that i need to add a new column with a not null and default value.
the sql is simple:
alter table my_table add new_col char(5) default ''
this runs for more than 25 minutes as the table that it's adding to has over 70 columns and > 100,000 rows.
i tried various things like wrapping the alter within a transaction, also adding the column first as a nullable column, updating it with a default value using tablockx and holdlock, and then adding the not null constraint on the column.
any input/solution?
thanks in advance.
update:
the problem was resolved and the slow down isolated to the transaction logging file growing and causing so many i/o reads that the query became long running. truncating the transaction log to a decent size and setting the file growth at a reasonable percentage optimized the query to under 8 minutes for half a million rows. (!)
