[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Change user-defined type

Greg Chagnon

3/19/2007 10:17:00 PM

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
10 Answers

weilu

3/20/2007 5:44:00 AM

0

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.microso...

Sincerely,

Wei Lu

Microsoft Online Community Support

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

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
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/de....

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

Razvan Socol

3/20/2007 6:04:00 AM

0

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 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.microso...
>
> Sincerely,
>
> Wei Lu
>
> Microsoft Online Community Support
>
> ==================================================
>
> Get notification to my posts through email? Please refer tohttp://msdn.microsoft.com/subscriptions/managednewsgroups/d......
> 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) athttp://msdn.microsoft.com/subscriptions/support/de....
>
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)

weilu

3/20/2007 10:12:00 AM

0

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....
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/de....

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

Greg Chagnon

3/20/2007 3:14:00 PM

0

"Razvan Socol" <rsocol@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

weilu

3/21/2007 3:17:00 AM

0

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....
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/de....

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

Razvan Socol

3/21/2007 7:57:00 AM

0

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

weilu

3/21/2007 11:49:00 AM

0

Hello Razvan,

I understand.

Well, nothing is perfect. I think the product team will consider to figure
out a better approach to modify the user-defined type.

Thank you anyway for your suggestion!

Sincerely,

Wei Lu

Microsoft Online Community Support

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

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
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/de....

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

Hugo Kornelis

3/21/2007 6:42:00 PM

0

On 21 Mar 2007 00:57:25 -0700, Razvan Socol wrote:

(snip)
>It would be a lot nicer if SQL Server would do all this when we use
>something like:
>
>ALTER TYPE Name TO nvarchar(70)

Hi Razvan,

Have you considered filing this as a suggestion on connect?

http://connect.microsoft.com...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Razvan Socol

3/21/2007 7:42:00 PM

0

> Have you considered filing this as a suggestion on connect?

David already posted it. Here is the link if you want to vote for it:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Feedbac...

Razvan

Hugo Kornelis

3/21/2007 8:07:00 PM

0

On 21 Mar 2007 12:42:05 -0700, Razvan Socol wrote:

>> Have you considered filing this as a suggestion on connect?
>
>David already posted it. Here is the link if you want to vote for it:
>https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Feedbac...
>
>Razvan

Thanks for the link, Razvan. I voted "4".

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...