Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven