MS SQL Server - changing nullability- any other options besides alter

Author

changing nullability- any other options besides alter

TJT

2006-10-24, 6:31 pm

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.

I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL

The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.

Just want to make sure that I am not missing anything here.... is there an
easier way to do this?

I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.

Any help would be greatly appreciated. Thanks.

MarkusB

2006-10-24, 6:31 pm

TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.

Mark

Kalen Delaney

2006-10-24, 6:31 pm

Hi Mark

NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"MarkusB" <m.bohse@quest-consultants.com> wrote in message
news:1159197893.908319.144320@i3g2000cwc.googlegroups.com...
>
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
>
> Mark
>

Sponsored Links