Re: automate tool to change column type?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 25 Oct 2006 14:49:59 -0700
ChrisA (ChrisA@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
SQL Server 2000/2005 Ent Mgr will allow you to jump into table designer
and change a column type. 2005 will capture the script, which involves
renaming the table, generating a new schema and then re-inserting the
values.
I have to perform this on some remote instances and won't know the
schema ahead of time. Is there a tool to change schema types (in-place)
that allows me to avoid making the change interactively??
First of all, is the change such that you can do it with ALTER TABLE?
Next question, is it suitable to do it with ALTER TABLE?
Implementing database changes is no kids game, but takes careful planning,
and the toys^H^Hols in Ent Mgr/Mgmt Studio are dangerous for this sort of
thing, but so can ALTER TABLE be.
If the channge is only a metadata change, like changing from varchar(10)
to varchar(20), then ALTER TABLE is swift as the eye, and what you should
use. But if the change requires the table to be rebuilt, then ALTER TABLE
will take the table offline for some time, which may or may not be
acceptable. Whether your change is of the latter kind is something you
should test.
And then there are changes that are not possible to make with ALTER
TABLE at all, because existing values needs to be transformed.
When you reload a table, there are a number of steps to take:
1) Create the new definition under a temporary name, including a
temporary name for the primary key.
2) Copy data over.
3) Move referencing foreign keys.
4) Drop the old table.
5) Rename the table and the PK to the correct name.
6) Restore indexes.
7) Restore triggers.
8) Restore foreign keys, check constraints and defaults.
There are a couple of possible way to vary this theme.
What you get in EM/Mgmt Studio does this, but never execute directly
from the GUI, but save and carefully review the script. There are a
couple of transformations you need to apply:
1) Do a search/replace on WITH NOCHECK to WITH CHECK. This will cause
the application of the constraints take longer time, but the optimizer
will be able to trust the constraints, which can have drastic effects
on performance
2) Remove all BEGIN and COMMIT TRANSACTION except for the first and
the last.
3) Remove all "go" in the script.
4) Embed all statements that are not execution of stored procedures in
EXEC().
5) If you are on SQL 2005, and BEGIN TRY END TRY around the entire
script and in the CATCH section add a ROLLBACK TRANSACTION
The transaction stuff could cause the transaction log to explode if
the tables are huge. You can skip it, if you decide to restore a
backup on any error.
Also, if you work in the GUI and find that you have been working in
the wrong table, close and start over. Else you may find that the
Table Designer includes the change anyway in the script.
It is not unlikely that there are better tools out there, but whatever
tool you use, you should still carefully review what is generated. As
I said, this is no kids game.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: SQL Server 2005 Backup Devices
- Next by Date: Re: Text Qualifier in a CSV file from results in SQL Server 2k5 Man. S
- Previous by thread: SQL Server 2005 Backup Devices
- Next by thread: Re: automate tool to change column type?
- Index(es):
Relevant Pages
|
