Database Reference
In-Depth Information
Listing 14-3.
CLR user-defined type usage
declare
@MicrosoftAddr dbo.USPostalAddress =
'One Microsoft Way, Redmond, WA, 98052'
,@GoogleAddr dbo.USPostalAddress =
'1600 Amphitheatre Pkwy, Mountain View, CA, 94043'
select
@MicrosoftAddr as [Raw Data]
,@MicrosoftAddr.ToString() as [Text Data]
,@MicrosoftAddr.Address as [Address]
,@MicrosoftAddr.CalculateShippingCost(@GoogleAddr) as [ShippingCost]
Figure 14-1.
CLR user-defined type usage
CLR user-defined types let you easily expand the SQL Server type library with your own types, developed and
used in object-oriented manner. It sounds too good to be true from a development standpoint and, unfortunately,
there are a few caveats about which you need to be aware.
As I already mentioned, SQL Server does not let you alter the type after you create it. You can redeploy the
new version of assembly with the
ALTER ASSEMBLY
command. This allows you to change the implementation of
the methods and/or fix the bugs in the implementation, although you would not be able to change the interface of
existing methods nor would you be able to utilize new public methods unless you drop and re-create the type. This
requires removing all type references from the database code.
All of this means that you must perform the following set of actions to re-deploy the type:
1.
Remove all type references from T-SQL code.
2.
Persist all data from columns of that type somewhere else, either by shredding type
attributes to relational format or casting them to
varbinary
. You need to be careful with
the latter approach and make sure that the new version of type objects can be deserialized
from the old object's binary data.
3.
Drop all columns of that type.
4.
Drop type, redeploy assembly, and create type again.
5.
Recreate the columns, and re-populate them with the data.
6.
Rebuild the indexes, reclaiming the space from the old columns and reducing the
fragmentation.
7.
Recreate T-SQL code that references the type.
As you see, that introduces a large amount of maintenance overhead, and it can lead to prolonged system
downtimes.
Performance is another very important aspect to consider. SQL Server stores CLR types in binary format. Every
time you access attributes or methods of CLR type, SQL Server deserializes the object and calls the CLR method,
which leads to overhead similar to what you saw in Chapter 13.
Let's run some tests and create two tables with address information: one using regular T-SQL data types and
another using a
dbo.USPostalAddress
user-defined type. You can see the code for doing this in Listing 14-4.
