Smith (n...@thank.you) writes:
I've got databases (both SQL Server 2000 and 2005) wherein an ID column is
used in many tables. I now need to extend this field from nvarchar(20) to
nvarchar(32). It's nvarchar since it contains both numbers and letters.
Ouch! This will be painful!
How do I go about this?
Do I drop PKs, FKs and indexes, alter the columns to nvarchar(32) and
re-create PKs, FKs and indexes?
That is one approach. There are some variations, but this is probably
the way to go.
I've read about disabling constraints. It isn't possible to disable PKs?
But is it okay to disable FKs and alter column which is part of the FK?
You cannot disable PKs. And you cannot just disable an FK and fiddle
with the PK columns.
Dropping / disabling PKs, FKs and indexes will not impact the content of
the tables?
It will not.
Is there an easy way to generate the script that will make the changes
or do I need to "write it by hand"? Is it possible to use "Auto generate
change scripts" feature?
You can use the Table Designer, but this tool is crappy and the script
it generates needs a lot of post-processing to be safe. It will also
create new tables and copy data over rather than using ALTER TABLE. But
you could this this script as a starting point. That is, make the
changes, generate the script and then all commands to drop and recreate
constraints from it.
Take out everything about transactions (see below). Also make sure
to change all WITH NOCHECK to WITH CHECK. You want all constraints to
be rechecked when you recreate them, so that the optimizer can trust
them.
You also need to decide on a disaster strategy. If your script fails half-
way through, are you content with reloading a backup? Or is it a
requirement that everything is rolled back? I would recommend the
former, because that will help to the keep the transaction log in check.
And making the script transaction-safe takes extra skill and effort.
--
Erland Sommarskog, SQL Server MVP,
esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
