Although user-defined types have been available in SQL Server for several years, they were not an especially powerful tool for DBAs and data modelers. T-SQL user-defined types are essentially synonyms for sized type declarations. For instance, you could define a type called ZipCode that maps to a char(5), to be used for representing US five-digit postal codes. Although this can be useful in some cases, it never really caught on as a widely accepted way of defining data. Most DBAs did not bother to use the feature, and since SQL Server 2005 this functionality has been deprecated.
Slated to replace the not-so-powerful T-SQL user-defined types is a new breed of CLR types that can represent virtually any data structure, as described in the sections that follow. These types are not mere wrappers over the intrinsic SQL Server types, as are T-SQL user-defined types. Rather, these types are full-featured class structures, complete with properties and methods. Implementation of these types can and should include such niceties as data verification and string formatting, which were not possible with T-SQL user-defined types.
Applications for User-Defined Types
User-defined types are excellent candidates for representing complex data that SQL Server’s intrinsic types don’t deal with well. For instance, a user might want to create a type to represent postal codes, instead of using one of SQL Server’s character datatypes. A postal code user-defined type could include logic for digesting and normalizing a variety of input string formats for various countries. In the United States, postal codes can be represented using five digits, with the format XXXXX, or nine digits, with the format XXXXX-YYYY. By defining logic within a postal code type to parse and process any type of input postal code format, we create a single library to deal with postal codes and thus eliminate repetition. To reduce repetition further, a series of properties or methods could be defined on the type for retrieving the postal code in various string formats.
Another application of CLR user-defined types is to extend the flexibility afforded by T-SQL from a software development perspective. For instance, many projects might benefit from an array datatype within SQL Server. In some situations it’s required that small, ordered sets of data be passed between stored procedures or user-defined functions. And although developers could use a variety of hacks to achieve functionality similar to arrays in previous versions of SQL Server, starting with SQL Server 2005, developers can leverage the .NET Framework to create an actual array that operates safely and efficiently within SQL Server.
Although the ability to create and use custom datatypes brings great power, developers should avoid the temptation to use SQL Server as a serialized object store. This is conceptually possible—business objects would simply need to be decorated with the correct attributes—but it would drive no real value within the database. Member properties of serialized objects cannot be efficiently queried, so any attempt to use the properties as predicates in queries would destroy server performance. Data represented by user-defined types should be atomic from a query perspective; no individual property of a type should be required in order to filter a row containing an instance of the type.
Adding a User-Defined Type to a SQL Server Project
To start a project for user-defined types in Visual Studio 2008, choose Database, and then select the SQL Server Project template, as shown in Figure. Set the reference to a development database and click OK to launch the project.
Opening a new project in Visual Studio 2008
Once the project has been created, right-click the project’s name in Solution Explorer, and then select Add ➤User-Defined Type, as shown in Figure. The first sample type for this chapter will be called PhoneNumber.
Adding a user-defined type to the project
Parts of a User-Defined Type
Upon adding a type to a project, Visual Studio 2008 will populate a stubbed version of the type with all of the pieces necessary to begin programming. The following code is the stubbed-out version of the PhoneNumber type as generated by Visual Studio 2008:using System;
This code is not as complex as it initially looks, and it can be broken down into a few different sections to make analysis easier.
A class or structure will not be treated as a user-defined type unless it is decorated with the SqlUserDefinedType attribute, as shown in the stubbed code. This attribute has a few parameters that define the serialization behavior of the type:
Note that the stubbed type is also decorated with the Serializable attribute. This attribute is also necessary; an instance of a type must be serialized any time it is written to disk or sent across the network.
A user-defined type must implement the INullable interface. This interface defines the Null and IsNull properties.
The IsNull property returns true if the type is null, in the SQL Server sense of the term (as opposed to the .NET sense of the term). null in C# (Nothing in VB .NET) is used to indicate that a reference type does not reference an object. In SQL Server, NULL means something different; it is a token used for unknown data, as well as uninitialized variables. Although the two are similar, it’s important to remember the distinction when working between the two platforms.
The IsNull property is controlled internally in the stubbed type by the value of the m_Null private member, but developers are free to implement this property in any way appropriate to the type being developed.
The Null property returns a freshly instantiated instance of the type. This instance should be initialized such that the IsNull property will return true. The Null property will be used by SQL Server any time a new instance of the type is requested (for example, when a variable is declared of that type). SQL Server will not call new, or an equivalent of new, directly. This means that private member initialization code can be put within the Null property instead of the constructor, if appropriate.
It’s important that user-defined types behave similarly to the intrinsic SQL Server datatypes. Therefore, care should be taken to make sure that the Null and IsNull properties behave correctly. Developers should make sure that these properties do not incorrectly identify null instances as non-null or non-null instances as null—doing so could severely damage the type’s ability to interact properly with the SQL Server engine. This is mainly controlled within the Parse method. A simple way to handle the situation is to always return Null (that is, the Null property of the type) if the string passed in to Parse is NULL (which you can check using the IsNull property of the SqlString type).
Every user-defined type must override the ToString method (which is inherited from the object class by every type defined in .NET). The rationale for this is flexible client interoperability. Although some client libraries may be able to consume a serialized instance of the type, others will only be able to make sense of the type represented as a string.
It is recommended that developers code the ToString method such that the string returned is compatible with the Parse method, described next. If these two methods are compatible, the string generated by the ToString method can be later passed back to SQL Server if necessary, in order to reinstantiate the type.
The Parse method is the exact opposite of the ToString method. Instead of producing a string representation of an instance of the type, this method takes a string as input, returning an instance of the type generated based on the string.
Parse is quite important in the world of user-defined types, as it will be called any time a type’s value is set using the assignment operator (equal sign). Furthermore, public mutators (public members or public settable properties) cannot be set on null instances; any instance that is null must first be instantiated using Parse.
These concepts are best illustrated using a code example. Assume the presence of a user-defined type called PhoneNumber that has a public, settable property called Number. A user might attempt to define an instance of the type and set Number to a value using the following code:DECLARE @phone PhoneNumber
This code will fail with the following error message:
Mutator 'Number' on '@phone' cannot be called on a null value
The following code would not result in an error, as it calls Parse internally:DECLARE @phone PhoneNumber
Unfortunately, the developer may have actually wanted to set the phone number using the property. That would require first calling Parse with a fake value to initialize the type, and then calling the property directly.DECLARE @phone PhoneNumber
In most cases, it’s probably a good idea to assign the value only using the assignment operator (and, therefore, Parse), but in some cases it will be necessary to initialize the type to allow for more straightforward SQL coding. The StringArray type shown later in this chapter provides a good example to illustrate that kind of situation.
A Simple Example: The PhoneNumber Type
A common requirement in virtually every business application is storing contact information. This information usually includes, among other things, names, mailing addresses, e-mail addresses, and phone numbers. Unfortunately, problems can sometimes occur due to formatting irregularities. Some people like to write US phone numbers using parentheses and dashes, as in (510) 549-5930. Some prefer to use only dashes, as in 510-549-5930. Others feel that periods look cool and will input the number as 510.549.5930.
It’s not difficult to handle these differences in format, but properly dealing with them requires that every stored procedure in the system validate the input. There should be exactly ten numerals in any valid US phone number. And preferably, those phone numbers should be stored in the database in exactly the same string format, such that they can be indexed and uniformly formatted for output purposes.
Instead of handling this validation and formatting in every stored procedure that deals with external phone number data, a CLR type can be defined. If every stored procedure uses this type, there will be no need for duplicate logic; all formatting will be handled by one central piece of code. Likewise, it will be guaranteed that the data is stored on disk in a uniform format, and output can be coded however necessary to meet business needs.
Modifying the Stubbed Code
Using the stubbed version generated by Visual Studio as a basis for defining the type, there is surprisingly little work required to develop a complete prototype. The first step is to clean up a bit andstub out the correct members for the project at hand. The following code shows the result of initialmodifications:[Serializable]
The various placeholder members have been replaced with a single private member, number. This variable is a string, and it will hold the validated phone number for a given instance of the type. The public property Number has also been added. This property currently directly sets the private member to the input value; some validation logic will have to be added in order to make it workable. Parse also now internally calls the Number property; that way, any validation logic for setting numbers will need to reside in only one place.
The Null and IsNull properties have also been modified to reflect the removal of the private member m_Null. Since US phone numbers must be exactly ten digits long, the validation logic will ensure that any number persisted within the type consists of ten digits. Any other time, number will be empty, and this will represent a null value.
ToString has been modified to simply return the value of number, the member variable that contains the phone number data. Since the return type is System.String instead of SqlString, this method cannot return a SqlString.Null value if the type is null, which would be preferable to make the type behave more similarly to the intrinsic SQL Server datatypes.
Finally, the properties of the SqlUserDefinedType attribute are changed to reflect the code. The format will be UserDefined, since strings are not value types in .NET. The serialization will be byteordered, allowing indexing and comparison on the type (see the next section on IBinarySerialize).
The type will not be fixed-length, since the empty string (null) case will occupy a single byte in serializedform, whereas properly populated phone numbers will occupy 10 bytes (1 byte per character in the phone number). Since user-defined types occupy 1 byte of overhead, the MaxByteSize parameter is set to 11. Ten bytes are allocated for the member data, and 1 byte is allocated for the type.
If you were to compile the code as listed in Visual Studio, it would compile cleanly. And if you were to manually deploy it (using CREATE ASSEMBLY), the resultant assembly would successfully register with SQL Server. However, CREATE TYPE (and, therefore, the Visual Studio deployment task) would fail with the following error:
Type "UserDefinedTypes.PhoneNumber" is marked for user-defined serialization, but does not implement the "System.Data.Microsoft.SqlServer.Server.IBinarySerialize" interface.
To implement the IBinarySerialize interface, add the name of the interface to the inheritance list in the class or structure declaration.
public struct PhoneNumber : INullable, IBinarySerialize
Visual Studio 2008 has a convenient feature to assist with implementation of interfaces. Rightclick the name of the interface after adding it, and a context menu will appear with an Implement Interface option. Click the suboption of the same name, as shown in Figure, to populate the type with the stubbed methods to implement the routine. Note that either Implement Interface or Implement Interface Explicitly will work. The latter explicitly prefixes methods and properties with the name of the interface to assist with multiple interface situations; however, this is not an issue with the PhoneNumber type.
Implementing an interface in the project
After the interface is implemented, the code for the type will contain a new region similar to the following:#region IBinarySerialize Members public void Read(System.IO.BinaryReader r)
The Read method is responsible for reconstituting an instance of the type from its binary serialized state; the Write method handles the serialization. Although this sounds somewhat complex, the methods of the BinaryReader and BinaryWriter classes are very simple to work with.
The BinaryReader class contains methods that can automatically handle many of the .NET types. These include ReadString, ReadInt16, ReadInt32, and others. Since the PhoneNumber type only deals with a single string (the private member number), the ReadString method alone is sufficient to rebuild an instance of the type from serialized form. The following code is the full representation of the Read method for the type:public void Read(System.IO.BinaryReader r)
The BinaryWriter class is even simpler than the BinaryReader class, with only a single method that developers will have to concern themselves with in most cases: Write. Several overloads are exposed for this method, such that what it offers is symmetrical to what is offered by the various Read methods of the BinaryReader. In the case of the PhoneNumber type, the overload that takes a string can be used.public void Write(System.IO.BinaryWriter w)
Again, this is all that’s necessary for implementing the method. And since the string will be serialized as a simple binary stream, this implementation also produces the byte ordering necessary for indexing and comparison.
Although many types will end up with more complex implementations than these, the basic pattern to keep in mind is that each call to one of the BinaryReader methods should have a corresponding call to Write, and vice versa. If you keep this rule in mind when working with the IBinarySerialize interface, development can be simple and efficient.
Implementing the Validation Logic
The final step in defining the PhoneNumber type is to implement the logic to validate the input. For thesake of this exercise, the logic can be quite simplistic: strip out all nonnumeric characters from the input string. If the resultant string of numerals is exactly ten characters long, it will be considered valid. Otherwise, it will be rejected with an error.
The following code is the completed Number property for the type:// Public mutator for the number
Note that the Regex, Match, and Matches classes are in the System.Text.RegularExpressions namespace, and the StringBuilder class is in the System.Text namespace. Appropriate using declarations need to be added before the classes to facilitate their use.
The complete code for the PhoneNumber type is as follows:using System;
Deploying and Testing the Type
Once the type is written, it is ready to deploy and test. The type can be deployed for debugging purposesdirectly from Visual Studio 2008. Right-click the project name in Solution Explorer and click Deploy, as shown in Figure.
Deploying the user-defined type
To try out the type, open SQL Server Management Studio and connect to the database that was specified when the project was created. User-defined types, once created, are instantiated using the DECLARE keyword, just like the built-in SQL Server datatypes. Recall the example from earlier in the chapter when we discussed the Parse method.DECLARE @phone PhoneNumber
This code creates a variable called @phone of type PhoneNumber. It then sets the value of the variable to the number for the Apress business phone line. Remember that this code is actually calling Parse on a null instance of the type.
To return the string representation of the type (the ten-digit phone number), the ToString method must be called, as in the following code:PRINT @phone.ToString()
Another important thing to remember is that methods and properties on user-defined types are case sensitive, even if the server or database isn’t. Note that the capitalization of ToString in the example is the same as the capitalization in the type’s definition.
Selecting the type without using ToString will return the type in binary serialized form. This form may be usable from a .NET application that has a reference to the assembly in which the type is compiled, but generally speaking, ToString will be a more commonly used way of getting a type’s data. Printing the type using the T-SQL PRINT statement is also possible and requires using either ToString or the CONVERT function, to convert the type into NVARCHAR.
Another Example: The StringArray Type
While the PhoneNumber type adequately illustrates the various programming nuances of working with user-defined types, it does not show off much of the power that can be gained from their use.
We’ll present here a more interesting example to satisfy a common requirement in SQL Server programming projects: representing data in an array format. This usually falls into the category of a developer needing to pass more than one value into a stored procedure, but arrays can also be used to make the T-SQL language more powerful from a programmatic standpoint. CLR user-defined types dramatically change the landscape such that these hacks are no longer necessary. In this example, the power of a .NET collection (the List class) will be exposed via a CLR user-defined type, resulting in a fully functional array that can be invoked directly from T-SQL.
Wrapping the Functionality of a Generic List
Starting with .NET 2.0, the framework includes support for containers called generics, which are strongly typed versions of the object-specific containers available in previous versions of the .NET Framework. Using the List<T> type (which is a generic version of the ArrayList) as a basis, a CLR user-defined type can be built to deal with collections of strings.
Generics allow developers to easily implement type-safe classes, such as collections. Most of the collection types in .NET 1.x were collections of objects. Since every type in .NET is derived from the object type, this means that every type can be cast as an object; therefore, every type could benefit from the collections. However, this also meant that a collection could not enforce what kinds of objects it stored. A collection might be incorrectly populated with both integers and strings, for instance. This could lead to exceptions when code meant to deal with integers suddenly encountered strings.
Generics solve this problem by allowing developers to specify a type to be used by a class (or collection) at object-creation time. This allows the CLR to enforce type-safety, allowing the object to use only the specified class. The syntax for this feature is a pair of angle brackets after the type name. For example, the following code creates an instance of List that can only use integers:List<int> myList = new List<int>;
Note that when implementing generic classes, a using directive for the System.Collections.
Generic namespace should be included at the top of the source file for the class. The actual string data will be held in a collection of type List<string>. This container will be strongly typed such that it can hold only strings. The following code defines the member:// The actual array
Next, the important features that make an array usable should be exposed by properties or methods such that they are accessible from T-SQL. These features include getting a count of strings in the array, adding strings to the array, removing strings from the array, and getting a string at a specific index of the array. The following block of code defines each of those features:public SqlInt32 Count
By simply wrapping the List<T>’s methods and properties, they are now accessible from T-SQL.
Implementing Parse and ToString
To instantiate an array, a developer will pass in a comma-delimited list of elements. The Parse method will handle the input, splitting up the list in order to populate the array. The ToString method will do the opposite, to return the contents of the array in a comma-delimited format.
The Parse method for the StringArray type uses the Split method of System.String. This method outputs an array of strings by splitting a delimited list. Once the array is produced, the method trims each element of preceding and trailing whitespace and puts any nonempty strings into the arr member variable. The following code implements the Parse method:public static StringArray Parse(SqlString s)
string strings = ((string)s).Split(',');
for(int i = 0; i < strings.Length; i++)
strings[i] = strings[i].Trim();
u.arr = new List<string>(strings.Length);
foreach (string str in strings)
if (str != "")
The ToString method does the reverse of Parse, using Join, which has the opposite behavior of Split. An array of strings is input, and a comma-delimited list is output.public override string ToString()
Defining the SqlUserDefinedType Attribute
Because the private member data for this type will reside in a reference type (List<T>), the format must be user defined.
It doesn’t make a lot of sense to compare two arrays for the purpose of sorting. There is no clear way to define how two arrays should sort. Should they sort based on number of elements? Based on the elements themselves? As it is nearly impossible to define how arrays would be sorted—and probably not useful for many development challenges—it also does not make sense to index a column of an array type. Indexes are generally helpful for seeking ordered data, but it is unlikely that a developer would want to perform a seek using an array as a key. For these reasons, there is no need to worry about byte ordering, so IsByteOrdered should be set to false.
And since arrays can contain any number of elements, the type is clearly not of a fixed length, nor does it have a maximum byte size (SQL Server 2008 has a byte limit of 2GB).
The fully populated SqlUserDefinedType attribute for this type is as follows:[Microsoft.SqlServer.Server.SqlUserDefinedType(
Determining how to serialize the data for this type will not be quite as simple as doing so for the PhoneNumber type. Instead of serializing a single piece of data, serialization for this type must deal with an array containing a variable number of elements.
A simple way of dealing with this situation is to first serialize a count of elements in the array, and then loop over and serialize each array element one by one. The only open issue with such a scheme is serialization of null-valued types. This can be easily taken care of using the following code, which serializes –1 as the count if the type is null:if (this.IsNull)
Non-null types, on the other hand, can be written using the following code, which first serializes the count of items and then each element in the array:w.Write(this.arr.Count);
Reading back the serialized data involves doing the exact opposite. First, the serialized count of items is read back. If it is –1, there is nothing else to do; the type will already be null. If the count is greater than –1, a loop will run to read each element from the serialized binary. Remember that 0 is also a valid count. Empty arrays are not the same as null arrays.
The entire code for implementing IBinarySerialize for the StringArray type is as follows:#region IBinarySerialize Members
Defining the IsNull and Null Properties
Implementing the INullable interface for the StringArray type is necessary in order to complete development of the type. In keeping with the theme of thinly wrapping the functionality of the .NET List<T> type, the IsNull method can be coded to determine whether the type is NULL based on whether the private member array is null—that is, whether it has been instantiated yet. Due to the fact that the array is not instantiated until data is passed into the Parse method, the Null method can simply call the default constructor and return the instance of the type. The following code implements both of these properties:public bool IsNull
Complete StringArray Class Sample
The complete code for the StringArray user-defined type is as follows:using System;
Using the StringArray
The StringArray type can be used to solve development problems that might require the full power afforded by a CLR function or stored procedure, but are easier to handle using data structures that are not built into SQL Server. An instance of the StringArray can be initially populated from a comma-delimited list, as in the following code:DECLARE @array StringArray
As a result of this code, the @array variable contains three elements, which can be retrieved or deleted using the GetAt or RemoveAt methods. An extension to the type might be to add a SetAt method to replace existing values in the array, but we’ll leave that as an exercise for interested readers.
Interestingly, the SQL CLR engine only blocks modification of null types via public mutators—public methods that happen to perform modification are allowed. So using the AddString method is an option at any time, whether or not the type is null. The following code will have the same end result as the previous code.DECLARE @array StringArray
Managing User-Defined Types
If an assembly has been loaded into the database using CREATE ASSEMBLY, types can be created or dropped without using the Visual Studio deployment task, as assumed in most examples in this chapter.
To manually create a type that is exposed in an assembly, without using the deployment task, use the T-SQL CREATE TYPE statement and specify the name of the assembly and name of the structure or class that defines the type. The following code creates the StringArray type from an assembly called StringArray:CREATE TYPE StringArray
To drop a type, use DROP TYPE. A type cannot be dropped if it is referenced by a table or function. The following code drops the StringArray type:
DROP TYPE StringArray
The sys.types catalog view exposes information about both system and user-defined types. To enumerate the data for CLR user-defined types in the database, use the is_assembly_type column:SELECT *
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.