Database Reference
In-Depth Information
Chapter 14
CLR Types
CLR types is another area of Common Language Runtime integration in SQL Server. User-defined CLR types allow
us to expand the standard type library by developing .Net classes and registering them as user-defined types in the
database. Standard CLR types, such as Geometry , Geography , and HierarchyId , provide built-in support for spatial
and hierarchical data.
In this chapter, you will learn about both user-defined and system CLR types.
User-Defined CLR Types
SQL Server has supported user-defined types (UDT) for years. Historically, T-SQL based user-defined types were used
to enforce type consistency. For example, when you needed to persist U.S. postal addresses in a several tables, you
could consider creating a PostalState UDT to store state information, as shown in Listing 14-1.
Listing 14-1. Creating a T-SQL user-defined type
create type dbo.PostalState from char(2) not null
Now you can use PostalState as a data type that defines table columns, parameters, and SQL variables. This
guarantees that every reference to the postal state in the database has exactly the same format: a non-nullable, two-
character string.
This approach has a few downsides, though. SQL Server does not permit altering of type definition. If, at any
point in time you need to make PostalState nullable or, perhaps, allow full state names rather than abbreviations, the
only option is to drop and recreate the type. Moreover, you must remove any references to that type in the database in
order to do that.
Tip
You can alter the type of the column to the base data type used by UDT. This is a metadata-only operation.
T-SQL user-defined types are always delivered from the scalar T-SQL type. For example, you cannot create a
T-SQL user-defined data called Address , which includes multiple attributes. Neither can you define check constraints
on the type level. Constraints still can be defined individually on the column level, although such an approach is less
convenient.
You can perform validation on the type level by binding the rule object to UDT. It is not recommended, however,
as rules are deprecated and will be removed in a future versions of SQL Server.
Note
 
 
Search WWH ::




Custom Search