Adding constraint to very large table

Question

Post reply

Adding constraint to very large table

Vik Nat

Hall of Fame

Points: 3930
More actions
April 19, 2005 at 3:54 pm

#166584

Hello,
I ran the following statement in test and it took forever to run and the trans log grew by several GB's.
alter table table_name add [col] [int] NOT NULL CONSTRAINT [CON] DEFAULT (0)
go
I know its not the alter table statement to add the column, but likely the Constraint that I am adding that needs to add the default value to all the rows in the table. The table has 4 million records. Is there a way to loop over or use rowcount or something and do this using cursors or commit transaction per row, so that the transaction log stays in check. Thanks in Advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Add column with null first

Add constraint with nocheck to exists with default value.

Update existing rows with default value using rowcount or what ever.

noeld SSC Guru Points: 96590 More actions · Answer 2

alter table table_name add [col] [int] NOT NULL CONSTRAINT [CON] DEFAULT (0) WITH VALUES

go

* Noel

Vik Nat Hall of Fame Points: 3930 More actions · Answer 3

Hi Guys,

Sorry - I have been distracted with other projects. But am looking at this issue this morning. So here is what I did:

I added the column without the constrant for the default value and then looped over each record using a cursor and updated the column with the default values. This kept my transaction log in check.

Now I am going to add the constrant (default value = 0). The question - I have for you guys is I do not want to the constraint to check that column for default values again as the values have already been inserted, I want it to enforce the contraints for all new records inserted from now on.

So will something like this work?

ALTER TABLE table_name WITH NOCHECK ADD CONSTRANT [CONSTRAINT_NAME] DEFAULT (0) FOR [COL_NAME]

Thanks in Advance for all your help

noeld SSC Guru Points: 96590 More actions · Answer 4

Yes, that will do what you need.

Becareful to really ensure that you don't need that initial check!

* Noel

Vik Nat Hall of Fame Points: 3930 More actions · Answer 5

Thanks Noel,

Just to make sure - by running this:

ALTER TABLE table_name WITH NOCHECK ADD CONSTRANT [CONSTRAINT_NAME] DEFAULT (0) FOR [COL_NAME]

After I run this, the default constraint will be enforced on all new records inserted into this table after this, correct? That is - I do not need to enable anything after this?

Vik Nat Hall of Fame Points: 3930 More actions · Answer 6

Thanks guys - I ran it last night in Prod and it worked like a charm

noeld SSC Guru Points: 96590 More actions · Answer 7

noeld

SSC Guru

Points: 96590

May 5, 2005 at 1:06 pm

#557206

Good for You!!!

* Noel