Altering column on very big table

Question

Post reply

Altering column on very big table

Sander A.

Default port

Points: 1440
More actions
January 22, 2010 at 4:58 am

#219828

I have a very big table (200 million records) with 4 columns that currently allow NULL values.
Now i want to change these columns to NOT NULL:
ALTER TABLE a
ALTER COLUMN b INT NOT NULL
But after running for 3 hours the tasks fails with a transaction log is full exception.
The only solution i can come up with is creating a second table with the same structure (except this one has the NOT NULL columns), copy all 200 million records to this table (in small batches ofcourse), and change all foreign keys referencing the old table to the new table.
As you can understand this is quite a lot of work, are there other sollutions??

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Did you try to alter the table using T-SQL command or GUI?

If it was GUI, try T-SQL. It may work.

Sander A. Default port Points: 1440 More actions · Answer 2

This sort of changes i always do with T-SQL. The GUI s*cks and does things you don't want to do most of the time.....

(just my humble opinion ofcourse)

Sander A. Default port Points: 1440 More actions · Answer 3

Sander A.

Default port

Points: 1440

January 25, 2010 at 1:42 am

#1108041

Anyone ???

DavidZahner Ten Centuries Points: 1169 More actions · Answer 4

I would create an index on the column(s) I want to alter if there isn’t already one and if the log is filling up, can you set it to simple recovery or if it is already, do you have space on the server to add a second log temporarily to act as an overflow which you could subsequently remove after the alter.

Sander A. Default port Points: 1440 More actions · Answer 5

There is 600gb free space for the log to (auto)grow and the recovery mode is already simple. But this still isn't enough.

I'm currently working on a script of creating a second table and moving all the data, it looks lik thisis the only way to do this 🙁

Sander A. Default port Points: 1440 More actions · Answer 6

DavidZahner (1/26/2010)
I would create an index on the column(s) I want to alter if there isn’t already one

There were indexes on them, but i had to drop them before altering the columns. When i don't drop the indexes i get the error "The index "xxxxx' is dependent on column 'xxxxx'. "

DavidZahner Ten Centuries Points: 1169 More actions · Answer 7

Rather than copy all of the data into a new table, you might try creating a new not null column on the existing table and copying the data into the new column in batches (I believe that this is what SQL Server is doing when you alter the column just not in batches), then just rename the old and new columns. The only down side to this is the ordinal position of the columns will probably change (unless the column is the last one) and any Select * statements in code will fail (hopefully you don’t have any).

Sander A. Default port Points: 1440 More actions · Answer 8

The ordinal position isn't an issue, those aren't in the right order anyway.

But how can i add a column which is NOT NULL while it is empty. Than it would need a default value which must be filled in for all records when i create this. So in this case i have to modify every record 2 times....

DavidZahner Ten Centuries Points: 1169 More actions · Answer 9

Okay, you’re right- wasn’t giving a whole lot of thought to my previous postings. So I haven’t tried this and am not working today to test it on any of my bigger tables, but what about keeping the indexes on the column(s) and just adding named not null constraints rather than changing the column definition to include not null. I believe that this should simply validate the current entries with the use of the existing index.

Sander A. Default port Points: 1440 More actions · Answer 10

A named check doesn't have the same advanteges for the plan-compiler as the NOT NULL has (i believe). I just want the tables to be 100% correct, if i have to do a massive data move once it is worth it for me....

DavidZahner Ten Centuries Points: 1169 More actions · Answer 11

In looking at a white paper for “Distributed/Heterogeneous Query Processing in Microsoft SQL Server” (http://ieeexplore.ieee.org/xpl/freeabs_all.jsp?arnumber=1410211, I know this isn’t a distributed query, but it’s what I had here and would still apply), the section on Constraint Property Framework states that the optimizer does consider constraints for pruning the search and updating the domain of possible values. A more readily available example I think would be the constraints required on partitioned tables and views that are used by the optimizer. So it appears as though the same plan would be generated, but I would need to wait until tomorrow to confirm that.