CLR Types - Pro SQL Server Internals - page 270

Keeping all of this in mind, we can conclude that T-SQL user-defined types have very limited use in SQL Server.
CLR user-defined types, on the other hand, addresses some of these issues. They allow you to create the complex
types with multiple attributes/properties, defining the data validation rules for the type and, finally, implementing the
methods that you can use to enhance the functionality of the type.
As an example, let's look at the implementation of the type that represents a simplified version of a U.S. postal
address. The code for this is shown in Listing 14-2.
Listing 14-2. CLR user-defined type
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(
Format.UserDefined,
ValidationMethodName = "ValidateAddress",
MaxByteSize=8000
)]
public struct USPostalAddress : INullable, IBinarySerialize
{
// Needs to be sorted to support BinarySearch
private static readonly List<string> _validStates = new List<string>
{
"AK","AL","AR","AZ","CA","CO","CT","DC","DE","FL","GA","HI","IA"
,"ID","IL","IN","KS","KY","LA","MA","MD","ME","MI","MN","MO","MS"
,"MT","NC","ND","NE","NH","NJ","NM","NV","NY","OH","OK","OR","PA"
,"PR","RI","SC","SD","TN","TX","UT","VA","VT","WA","WI","WV","WY"
};
private bool _null;
private string _address;
private string _city;
private string _state;
private string _zipCode;
public bool IsNull { get { return _null; } }
public string Address
{
[SqlMethod(IsDeterministic = true, IsPrecise = true)]
get { return _address; }
}
public string City
{
[SqlMethod(IsDeterministic = true, IsPrecise = true)]
get { return _city; }
}
public string State
{
[SqlMethod(IsDeterministic = true, IsPrecise=true)]
get { return _state; }
}