Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Change user-defined type


I thought I had mentioned this before, but...

It would be GREAT if we could change a user-defined type even when columns
in tables are using that type.  In fact, it seems more likely to want to
change a user-defined type when lots of columns are defined using that
type.

It really detracts from the usefulness of this feature when a user-defined
type can't be redefined.  

I'll post a "wish" for this to be allowed.

David Walker

Hello David,

Thank you for your providing.

IMO, this behavior could invalidate data in the tables or indexes.That's
why we did not involve this feature in the User-defined Type.

You could send your request directly to the product team where they are
monitor:

http://connect.microsoft.com/sql

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.asp...
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

-----------------------------------------------Reply-----------------------------------------------

It would be nice if in this case SQL Server would behave as if it
would execute automatically an ALTER TABLE <tbl> ALTER COLUMN <col>
<newdatatype> for each table involved, inside a transaction (so it
would rollback all changes if the data in any table could not be
converted).

Razvan

On Mar 20, 7:43 am, w@online.microsoft.com (Wei Lu [MSFT]) wrote:

Hello Razvan,

I think you could do this by yourself that you could include all the Alter
column operation in one transcation.

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.asp...
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

-----------------------------------------------Reply-----------------------------------------------

"Razvan Socol" <rso@gmail.com> wrote in
news:1174370657.939475.171290@l75g2000hse.googlegroups.com:

> It would be nice if in this case SQL Server would behave as if it
> would execute automatically an ALTER TABLE <tbl> ALTER COLUMN <col>
> <newdatatype> for each table involved, inside a transaction (so it
> would rollback all changes if the data in any table could not be
> converted).

> Razvan

Right, that's exactly what I am asking.

As things stand, you have to change the definition of all fields in all
tables that use the user-defined type, then change the user-defined
type, then REMEMBER what all fields previously USED that user-defined
type if you want to have them use that user-defined type again.

This last part -- keeping track of just which fields had used that type
-- is maybe the hardest part.

My first thought was that the PURPOSE of user-defined types was so that
you COULD easily change the type and have it applied everywhere.  So
obvious!

Wei Lu's comment (which follows this one) suggests that you can do all
this in one transaction.  That is true, IF you have some external
mechanism to keep track of which fields use the user-defined type.

Which kind of defeats the purpose of the user-defined type in the first
place!

Wei Lu's first comment, that such a chnage could invalidate data in
tables or indexes, doesn't make much sense to me.  How could changing a
data type invalidate data (other than any other change that might
already invalidate data, like changing an Int to a Smallint?  This kind
of ALTER statement is already checked for by SQL Server when you use
ALTER.

I generally envision this change to make fields bigger (Int to Bigint),
or to make Char or Varchar fields wider, etc.  Still, since you can
already ALTER column definitions, SQL could internally do what I am
asking.

I posted a request at connect.microsoft.com/sql.  We'll see if anyone
votes on it.  (Many requests there don't get ANY votes.)

David Walker

-----------------------------------------------Reply-----------------------------------------------

Hello David,

Thanks for the update and further information.

Since the type is uder defined, sql server could not know how the user will
change the definition.

For example, if a user defined a Location type as a 2D coordinate (x, y).

And then, the user may try to modify it to the longitude and latitude, the
data may be invalid.

Anyway, IMO, your suggestion is valuable. And I think product team will
consider some modification or enhance for the User-defined Type.

Thank you!

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.asp...
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

-----------------------------------------------Reply-----------------------------------------------

I think David and I were thinking about User Defined Types as they
were since SQL 2000, i.e. as an alias for standard type. For example,
consider the Name UDT in AdventureWorks. There are 80 columns in this
database (in tables, but also in views) that use this UDT. What should
we do if we want to change this UDT to be nvarchar(70) instead of
nvarchar(50) ? Probably something like this (in a transaction):

1. Get a list of all the columns using that UDT
2. Drop all views that reference those columns (after storing their
definition somewhere)
3. Change those columns to the base data type, so they don't reference
the UDT anymore (using ALTER TABLE tbl ALTER COLUMN col nvarchar(50),
for example)
4. Drop the UDT
5. Recreate the UDT as nvarchar(70)
6. Change all those columns to use the UDT again
7. Recreate the views (with the same definition) (in some logical
order, so we don't reference views that are not created yet)

It would be a lot nicer if SQL Server would do all this when we use
something like:

ALTER TYPE Name TO nvarchar(70)

Razvan

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc