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. devx-admin Previous Next Share the Post: Electric Vehicle Battery Breakthrough Grace Phillips October 2, 2023 The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years, Virginia’s Clean Economy Act Soars Ahead Lila Anderson October 2, 2023 Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) Innovative Energy Storage Solutions Jordan Williams October 2, 2023 The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and Revolutionizing India’s Tech Sector with Renesas Noah Nguyen October 2, 2023 Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” Thrilling East Windsor Mixed-Use Development Johannah Lopez October 2, 2023 Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of Top Software Development Companies in USA Johannah Lopez October 1, 2023 Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies Electric Vehicle Battery Breakthrough Grace Phillips October 2, 2023 The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years, as reported by energy analytics Virginia’s Clean Economy Act Soars Ahead Lila Anderson October 2, 2023 Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy Innovative Energy Storage Solutions Jordan Williams October 2, 2023 The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will Revolutionizing India’s Tech Sector with Renesas Noah Nguyen October 2, 2023 Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on Thrilling East Windsor Mixed-Use Development Johannah Lopez October 2, 2023 Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of constructing residential and commercial buildings. Top Software Development Companies in USA Johannah Lopez October 1, 2023 Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a Top Software Development Companies Noah Nguyen September 30, 2023 Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in Top Web Development Companies in India Jordan Williams September 30, 2023 In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to Top Web Development Companies in USA Johannah Lopez September 30, 2023 Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your Inside Michigan’s Clean Energy Revolution Grace Phillips September 29, 2023 Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting European Chips Act: What is it? Lila Anderson September 29, 2023 In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its You Should Use Low-Code Platforms for Apps Jordan Williams September 29, 2023 As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not Five Powerful Strategies to Bolster Your Cybersecurity Noah Nguyen September 29, 2023 In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies Tech Layoffs Are Getting Worse Globally Jordan Williams September 29, 2023 Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data Huawei Dazzles with Electric Vehicles and Wireless Earbuds Johannah Lopez September 29, 2023 During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting Digital Banking Needs Cybersecurity Johannah Lopez September 29, 2023 The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising Terry Clune’s Fintech Empire Grace Phillips September 29, 2023 Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned The Role Of AI Within A Web Design Agency? DevX Editor September 29, 2023 In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing The Future of AI and the Investment Opportunities it Presents DevX Editor September 29, 2023 There is no doubt that modern technology has changed the way we live and work forever. Nowadays, there is a wide array of different types of technologies such as AI Is Generative AI the Next Internet? Lila Anderson September 29, 2023 The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These The New Surface Laptop Studio 2 Is Nuts Noah Nguyen September 29, 2023 The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over GPU-Accelerated 5G in Japan Johannah Lopez September 28, 2023 NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will AI Journalism: Balancing Integrity and Innovation Grace Phillips September 28, 2023 An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These Big Deal Days Extravaganza Lila Anderson September 28, 2023 The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created Cisco Splunk Deal Sparks Tech Acquisition Frenzy Jordan Williams September 28, 2023 Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the Show More