If you have a column of a table that needs to be changed, don't always go to the modify table page in SQL manager to do your changes. I tried something simple as increasing the column size of a nvarchar field, but the update kept on giving timeouts in the SQL manager. Then I tried the generate SQL script button to see what SQL manager was trying to do. The contents gave me an idea why I was getting timeouts:
-alter the table:
-drop constraints
-drop triggers
-create temporary table with new column sizes/fields
-add defaults from table to temp table
-add constraints from table to temp table
-add index to temp table
-insert all values from original table to temp table
-drop relationships from original table
-drop original table
-rename temp table name to original table name
-add relationships to temp table
Which it needed to do is:
ALTER TABLE sometablename
ALTER COLUMN somecolumnname
nvarchar(100) NULL;
Next time if I modify a table with much data, or a lot of relationships and/or constraints I will stay clear of the SQL manager "modify table" dialog.
--To add a column:
ReplyDeleteALTER TABLE sometablename
ADD somecolumnname nvarchar(100) NULL;
--Add multiple columns:
ALTER TABLE sometablename
ADD ( column_1 column_definition,
column_2 column_definition,
...
column_n column_definition );
--Modify column:
ALTER TABLE sometablename
MODIFY somecolumnname column_type;
--Modify multiple columns:
ALTER TABLE table_name
MODIFY ( column_1 column_type,
column_2 column_type,
...
column_n column_type );
--Drop column:
ALTER TABLE table_name
DROP COLUMN column_name;