Suggestions how to handle data structure changes

8 Jul 2005 1:06 AM

Peter

I need some suggestions how to handle data structure changes. For example, I
have a column which is char(10) and it appears in many tables. Also, it has
corresponding variables and parameters in stored procedures/triggers. I
want to change that column to char(20) now and may be another size in the
future. What is the fastest way to make all the changes? I think about
using user-defined data type (UDDT) but I find out you cannot change the size
of UDDT unless you drop all the objects using it. (Note: I'm not sure whether
this is true if you use UDDT in stored procedures and triggers but at least
in tables). So, it seems that UDDT will not be a good solution.

Thanks for any help.

8 Jul 2005 2:47 AM

Brian Selzer

You are correct that sp_droptype will fail if any database objects were
created with it. It does, however, store the type distribution in the
INFORMATION_SCHEMA views, You could generate ALTER TABLE ALTER COLUMN
statements from them to convert the data type for the columns to their base
type, drop the views, functions and procedures, drop the type, recreate the
type, alter the data type for the columns back to the udt, and finally
recreate the views, functions, and procedures. Views, functions and
procedures are text, stored in syscomments, so the text wouldn't have to be
changed to regenerate them using the new data type. It's pretty easy to
generate scripts in Enterprise Manager to drop and recreate the procedures,
views, and functions. If the datatype is used in PK or FK constraint, you
have to drop those constraints as well. There may be other headaches as
well. See the ALTER TABLE section in BOL.

Changing a data type is a real pain. I use surrogate keys to insulate me
from this kind of change. Since each atomic value is only stored in one
place, changing the datatype of its column has minimal impact on the overall
database--only one table need be changed.

Show quote

"Peter" <Pe***@discussions.microsoft.com> wrote in message
news:CCB5542C-DB41-4079-8095-4880386883DB@microsoft.com...
> I need some suggestions how to handle data structure changes. For
example, I
> have a column which is char(10) and it appears in many tables. Also, it
has
> corresponding variables and parameters in stored procedures/triggers. I
> want to change that column to char(20) now and may be another size in the
> future. What is the fastest way to make all the changes? I think about
> using user-defined data type (UDDT) but I find out you cannot change the
size
> of UDDT unless you drop all the objects using it. (Note: I'm not sure
whether
> this is true if you use UDDT in stored procedures and triggers but at
least
> in tables). So, it seems that UDDT will not be a good solution.
>
>
> Thanks for any help.