he next version of SQL Server (code name Yukon) has extensive support for the Common Language Runtime (CLR).

Previous versions of SQL Server (2000 and earlier) had a mechanism for creating custom data types. These data types were nothing more than aliases to system data types. In Yukon, you can create your own fully functional custom data types.The process of creating a UDT begins with creating a .NET class that supports the proper API. Creating and using a UDT comes in two phases: creating the library and registering the UDT with SQL Server. The first step is to create your new data type in Visual Studio .NET. The listing for this article shows how to create a Social Security Number data type. When you create your UDT you will do the following:

• Create a new class library
• Import assembles
• Add two attributes to your class (Serializable() and SqlUserDefinedDataType)
• Implement the INullable interface
• Add required methods and properties to your class
• Compile your class
• Register your class library with SQL Server

Creating a Class Library
The initial phase of this process is to create a new class library. You create a new class by selecting New and then Project from the Visual Studio .NET menu. Select Class Library from either the C# or Visual Basic.NET (VB.NET) Projects list (this article demonstrates using VB.NET).

After creating your class library, you need to import the following assemblies

• System
• System.Data.SQL
• System.Data.SQLTypes

Adding Attributes
After adding the appropriate references, you need to “decorate” the class with two attributes. These attributes are Serializable() and SqlUserDefinedType. The Serializable attribute gives the CLR the ability to take the class and serialize it or to turn it into XML. SQL Server uses this capability to store and retrieve the class from its data store. The SqlUserDefineType attribute is used by SQL Server to determine how it should manage your class. This attribute has a number of properties. The two most important are Format and MaxByteSize. The Format property instructs SQL Server how to store the object. The MaxByteSize property determines how many bytes your data type can consume.

Implement the Object Interface
After decorating the class, you need to implement the .NET Framework INullable Interface. Interfaces are implemented using the Implements statement. The following snippet demonstrates how to implement the INullable interface.

   Public ReadOnly Property _   IsNull() As Boolean        Implements _    System.Data.SqlTypes_    .INullable.IsNull      Get        Return Me._IsNull      End Get   End Property

After implementing the INullable interface, you are required to implement the ToString() and Parse() methods and the Null() property

The ToString method is a common function found on most .NET classes. This function returns a representation of your data type as a string. Remember when you return the value from the ToString function you need to include a representation of NULL values. The following snippet shows the ToString method for this article’s example.

   Public Overrides Function_     ToString() As String     If Me.IsNull Then       Return "NULL"     Else       Return Me._cInternalValue     End If   End Function

The other method that needs attention is the Parse() method. This is the method that SQL Server calls whenever someone inserts or updates data defined as your data type. The API for this method is as follows:

   Parse(cData as SqlString) _     as 

As you can see, this function receives a parameter declared as type SqlString. Upon receiving this code, you perform a number of tasks. The first task is to determine whether or not the data passed in is NULL. If it is, return Nothing from this method.

The second task is to convert the passed-in data to a .NET data type. This is done with the Convert class. After converting the passed-in data to a .NET data type, you can validate the data. If the data passed in does not conform to your rules, you can throw an exception. Figure 1 shows what an error thrown from a custom data type looks like.

Registering the Class After you have created and compiled the class into a DLL, you need to go to SQL Server and perform two tasks. Register the assembly with SQL Server. Register the type with SQL Server. The following SQL code demonstrates how to perform these tasks: CREATE ASSEMBLY SSNLIbrary FROM 'c: estSSNLibrary.dll' CREATE TYPE [DpsiSSN] EXTERNAL NAME [SSNLibrary]:[SSNLibrary.DpsiSSN] Using the Class Once you have registered the class, you can begin using that class as a data type in a table definition. When you have created a table supporting your data type, you can begin adding data to your table. The following code snippet shows how to create a table using your custom data type, how to insert data into your table, and how to query data from a column defined as your custom data type. -- Create table with data type CREATE TABLE SSNTEST (ssntestid int primary key not null, cssn DpsiSSN) GO -- Insert some data DECLARE @cParam DpsiSSN SET @cParam = CONVERT(DpsiSSN,'123456789') INSERT INTO ssntest (cssn) VALUES (@cParam) SELECT cSSN::MySSN FROM ssntest WHERE Right(cSSN::MySSN,4) = '0000' SELECT cSSN::MySSN FROM ssntest ORDER BY cSSN::MySSN There are a couple of items to note from the above example. The first item is the use of the Cast() statement. In order to insert data into a custom data type, you need to cast the SQL Server data types into your own data types. The next item to note is the syntax for querying data from a custom data type. The syntax for querying data from a custom data type is ::. From my tests, I was able to sort, group and query by attributes contained in my custom data type. As you can see, creating custom data types in SQL Server Yukon is pretty simple stuff. Finally, you can create your own “real” data types with validation rules and all. I hope you can see now how to take advantage of this feature with minimal effort. Previous Next Share the Post: What is Metadata? June 1, 2023 What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular What We Should Expect from Cell Phone Tech in the Near Future May 31, 2023 The earliest cell phones included boxy designs full of buttons and antennas, and they only made calls. Needless to say, we’ve come a long way from those classic brick phones The Best Mechanical Keyboards For Programmers: Where To Find Them May 29, 2023 When it comes to programming, a good mechanical keyboard can make all the difference. Naturally, you would want one of the best mechanical keyboards for programmers. But with so many The Digital Panopticon: Is Big Brother Always Watching Us Online? May 26, 2023 In the age of digital transformation, the internet has become a ubiquitous part of our lives. From socializing, shopping, and learning to more sensitive activities such as banking and healthcare, Embracing Change: How AI Is Revolutionizing the Developer’s Role May 25, 2023 The world of software development is changing drastically with the introduction of Artificial Intelligence and Machine Learning technologies. In the past, software developers were in charge of the entire development The Benefits of Using XDR Solutions May 24, 2023 Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved How AI is Revolutionizing Fraud Detection May 23, 2023 Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across Companies Leading AI Innovation in 2023 May 22, 2023 Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several Step-by-Step Guide to Properly Copyright Your Website May 18, 2023 Creating a website is not easy, but protecting your website is equally important. Implementing copyright laws ensures that the substance of your website remains secure and sheltered. Copyrighting your website Fivetran Pricing Explained May 17, 2023 One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of Kubernetes Logging: What You Need to Know May 16, 2023 Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes Why Is Ransomware Such a Major Threat? May 15, 2023 One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing Show More