altering table taking ages in SSMS

Question

altering table taking ages in SSMS

winston Smith

SSCoach

Points: 19500
More actions
December 30, 2008 at 2:20 am

#129509

im altering a table in SSMS to remove a foreign key constraing, and re-add it with update cascade enabled. for even a small table this is taking 2+ hours. why is this and is there a quicker way?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Dugi SSCoach Points: 17998 More actions · Answer 1

How many records do you have in you table!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 2

Are you using the SSMS GUI or executing query for removing the FK.

If using the GUI then, some time it drops and recreate the tables. So first see the script for changes before you save the table change.

If executing the T-SQL then your table might have so many records and idexes on it.

Regards,
Nitin

Dugi SSCoach Points: 17998 More actions · Answer 3

I think better is to do with T-SQL !

Simple:

First Drop Constraint then

Add Constraint again

ALTER TABLE Your_Table

DROP CONSTRAINT constraint_name;

ALTER TABLE Your_Table

ADD CONSTRAINT constraint_name FOREIGN KEY (your_field) REFERENCES Ref_Table_Name(Ref_field) ON DELETE OR UPDATE CASCADE

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]