|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Changing the size of a user defined typeIs it possible and if so how do you change the size of a user defined type
with out dropping it. It has been used an many tables already and I need to increase it. Anthony This is my main problem with user-defined types and is exactly why I don't
use them. As far as I know, you will need to convert all columns to the longer base type, drop the type, re-create it with the new length, and convert them all back. Of course, this is a big problem if you have foreign key constraints, especially with circular references, that talk to columns of that data type. Also, stored procedures and udfs that reference the udt in parameters will have huge problems while the data type doesn't exist. Another approach is to rename the udt, create a new one with the old name, then go in and alter all tables to use the re-created type of the same name, then drop the old one. I believe you will need to recompile all of the stored procs and functions that reference the type as incoming parameters, but not if the routine only uses the type in local variables. You might have to test that thoroughly if you have both types (sorry for the pun), otherwise it would be safer to recompile all routines that reference the type at all. Are you reconsidering the use of user-defined types yet? A Show quote "Anthony" <Anth***@discussions.microsoft.com> wrote in message news:37A0B602-3D50-4DDC-A78C-60D04D3EAA4C@microsoft.com... > Is it possible and if so how do you change the size of a user defined type > with out dropping it. It has been used an many tables already and I need > to > increase it. > > Anthony Aaron,
Do you know if there is an underlying important reason why SQL server doesn't allow someone to change the definition of a UDT without all that gymnastics... Or are they just sadists? When I started using SQL server, I created a few UDTs while the database was in development but quickly scrapped that idea the first time I wanted to modify one. Luckily, I hadn't put it in production yet. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23lZfHAbqFHA.528@TK2MSFTNGP09.phx.gbl... > This is my main problem with user-defined types and is exactly why I don't > use them. > > As far as I know, you will need to convert all columns to the longer base > type, drop the type, re-create it with the new length, and convert them > all back. Of course, this is a big problem if you have foreign key > constraints, especially with circular references, that talk to columns of > that data type. Also, stored procedures and udfs that reference the udt in > parameters will have huge problems while the data type doesn't exist. > > Another approach is to rename the udt, create a new one with the old name, > then go in and alter all tables to use the re-created type of the same > name, then drop the old one. I believe you will need to recompile all of > the stored procs and functions that reference the type as incoming > parameters, but not if the routine only uses the type in local variables. > You might have to test that thoroughly if you have both types (sorry for > the pun), otherwise it would be safer to recompile all routines that > reference the type at all. > > Are you reconsidering the use of user-defined types yet? > > A > > > "Anthony" <Anth***@discussions.microsoft.com> wrote in message > news:37A0B602-3D50-4DDC-A78C-60D04D3EAA4C@microsoft.com... >> Is it possible and if so how do you change the size of a user defined >> type >> with out dropping it. It has been used an many tables already and I need >> to >> increase it. >> >> Anthony > > > Do you know if there is an underlying important reason why SQL server I think a lot of it has to do with the fact that query plans, procedure > doesn't allow someone to change the definition of a UDT without all that > gymnastics... Or are they just sadists? cache, index storage etc. all use the original definition, and I think it would be pretty difficult to go out and do this en masse, instead of an object-by-object process. Perhaps a part of it is laziness, too. If they can go out and correct all these things if I alter the column to a known type, they should be able to do the same thing if I change the type's definition. But I think it still goes back to the act of modifying a single object at a time vs. ripping a datatype out from under a whole slew of objects in one shot, like pulling the red carpet out from under a pack of papparazzi... they aren't all going to land on their feet. I'm going to have to run some experiments in SQL Server 2005 to see if any of this has been "improved." You can do this yourself using the INFORMATION_SCHEMA relatively simply, but
it is going to take you a while perhaps. I have done things like this in the past to change a type en masse, and it can be ultra painful: select 'alter table ' + table_name + ' alter column ' + column_name + ' <newtype> ' + case when IS_NULLABLE = 'Yes' then ' NULL ' else ' NOT NULL ' end from information_schema.columns where DATA_TYPE = '<oldtype>' Of course build your script to put it back to using the new type once you are finished. I suggest if you want it changed go to http://lab.msdn.microsoft.com/productfeedback/default.aspx and make a suggestion. Then post back here and get people to go vote for it. No question that this is a good idea to have it do it for you :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Raymond D'Anjou" <rdanjou@savantsoftNOSPAM.net> wrote in message news:evFyOLbqFHA.544@TK2MSFTNGP11.phx.gbl... > Aaron, > Do you know if there is an underlying important reason why SQL server > doesn't allow someone to change the definition of a UDT without all that > gymnastics... Or are they just sadists? > > When I started using SQL server, I created a few UDTs while the database > was in development but quickly scrapped that idea the first time I wanted > to modify one. > Luckily, I hadn't put it in production yet. > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:%23lZfHAbqFHA.528@TK2MSFTNGP09.phx.gbl... >> This is my main problem with user-defined types and is exactly why I >> don't use them. >> >> As far as I know, you will need to convert all columns to the longer base >> type, drop the type, re-create it with the new length, and convert them >> all back. Of course, this is a big problem if you have foreign key >> constraints, especially with circular references, that talk to columns of >> that data type. Also, stored procedures and udfs that reference the udt >> in parameters will have huge problems while the data type doesn't exist. >> >> Another approach is to rename the udt, create a new one with the old >> name, then go in and alter all tables to use the re-created type of the >> same name, then drop the old one. I believe you will need to recompile >> all of the stored procs and functions that reference the type as incoming >> parameters, but not if the routine only uses the type in local variables. >> You might have to test that thoroughly if you have both types (sorry for >> the pun), otherwise it would be safer to recompile all routines that >> reference the type at all. >> >> Are you reconsidering the use of user-defined types yet? >> >> A >> >> >> "Anthony" <Anth***@discussions.microsoft.com> wrote in message >> news:37A0B602-3D50-4DDC-A78C-60D04D3EAA4C@microsoft.com... >>> Is it possible and if so how do you change the size of a user defined >>> type >>> with out dropping it. It has been used an many tables already and I >>> need to >>> increase it. >>> >>> Anthony >> >> > >  |
|||||||||||||||||||||||
Other interesting topics