You can create user-defined types (UDTs) in SQL Server to extend the existing scalar type system in the database. In addition to using the existing scalar types, such as int, char, nvarchar, and uniqueidentifier, you can create your own atomic data types for creating tables and return values. The benefit is that you can store your data in a more natural way and access the properties and methods of the data type from within T-SQL or your .NET code.
Many people incorrectly conclude that SQL Server is now an object database server, but the UDT feature doesn't come close to fulfilling this fantasy. When you want to store data in a more natural format, however, this feature fulfills the need. For example, it might be advantageous to create a custom date type and time type to be used instead of the built in datetime type. Or you might want to store a distance given in feet and inches as a single value rather than two separate values. Remember that the UDT should be an atomic value and should be created only when it is appropriate to display your UDT value in a column.
A UDT must have a string representation for input and display of the data. It must also have a binary representation (for being persisted to disk) that cannot exceed 8000 bytes, and it must have a null representation because all scalars in SQL Server must be able to have a null value.
UDTs must be implemented on every database that uses them. For example, if you are planning to explicitly create temporary tables based on data that is in your current database and that data includes UDTs, the UDTs must also be registered in the tempdb database. As a side note, there is no need to register all of your types in the tempdb database even though SQL Server often implicitly creates temporary tables in tempdb that are used to help the SQL engine do its work.
A UDT can be created using a structure (C# struct) or a class, and the Visual Studio .NET template creates a structure. If you decide to change this to a class, you must add the StructLayout attribute with the LayoutKind.Sequential setting to the class to ensure that the fields in the class remain in the defined order when the object is serialized.
When creating a UDT in Visual Studio .NET, you use the SqlUserDefinedType attribute to tell Visual Studio how to register the type. This attribute has a property called Format that can be set to Native or UserDefined. If you set the format to Native, the SQLCLR provides the code to read and write the binary data, but all of your fields in this structure must be blittable. (Blittable fields are those whose managed and unmanaged byte representations are the same, so no conversion is required to marshal between managed and unmanaged code.) Here are the blit table types and other types you can use with Format.Native. Notice that string is not in the list. If you want to use a string field, you must set the Format property to UserDefined and you must provide the code to read and write the byte representation to disk.
System.Byte
System.SByte
System.Int16
System.UInt16
System.Int32
System.UInt32
System.Int64
System.IntPtr
System.UIntPtr
One-dimensional arrays of blittable types, such as an array of integers
System.Float
System.Double
SqlSingle
SqlDouble
SqlByte
SqlInt16
SqlUInt16
SqlInt32
SqlUInt32
SqlInt64
SqlUInt64
SqlDateTime
Another requirement when you use Format.Native is that you must assign the Serializable attribute to the UDT.
User-defined types can be sorted, indexed, and used in magnitude comparisons if the IsByte Ordered property of the SqlUserDefinedType attribute is set to true. The comparisons are always done using the binary representation of the UDT, so it's important to order your fields in your UDT so that comparisons can be made. Doing comparisons across the binary representations can be a big limitation if you are attempting to store many fields in the UDT, but if the UDT is implemented properly that is, implemented for truly atomic values this should not be a limitation.
The following code sample shows a UDT for a distance data type. The distance is atomic in that it is a single value called totalInches (but it is represented as feet and inches when it is displayed).
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _ Public Structure DistanceVb Implements INullable, IComparable Public ReadOnly Property Feet() As Integer Get Return CInt(totalInches / 12) End Get End Property Public ReadOnly Property Inches() As Integer Get Return totalInches Mod 12 End Get End Property Public Overrides Function ToString() As String Return String.Format("{0} ft. {1} in.", Feet, Inches) End Function Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull Get ' Put your code here Return m_Null End Get End Property Public Shared ReadOnly Property Null() As DistanceVb Get Dim h As DistanceVb = New DistanceVb h.m_Null = True Return h End Get End Property Public Shared Function Parse(ByVal s As SqlString) As DistanceVb If s.IsNull Then Return Null End If Dim u As DistanceVb = New DistanceVb Dim distance As String = s.Value If distance = "null" Then Return Null distance = distance.ToLower() Dim feet As Integer = 0 Dim inches As Integer = 0 Dim parts() As String = distance.Split(" "c) Dim feetLocation As Integer = Array.IndexOf(parts, "ft.") If (feetLocation > 0) Then feet = Integer.Parse(parts(feetLocation - 1)) End If Dim inchesLocation As Integer = Array.IndexOf(parts, "in.") If (inchesLocation > 0) Then inches = Integer.Parse(parts(inchesLocation - 1)) End If u.totalInches = (feet * 12) + inches Return u End Function Public Function CompareTo(ByVal obj As Object) As Integer _ Implements IComparable.CompareTo Dim other As DistanceVb = CType(obj, DistanceVb) Return totalInches - other.totalInches End Function Public totalInches As Integer Private m_Null As Boolean End Structure
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct DistanceCs : INullable, IComparable { public int Feet { get { return totalInches / 12; } } public int Inches { get { return totalInches % 12; } } public override string ToString() { return string.Format("{0} ft. {1} in.", Feet, Inches); } public bool IsNull { get { return m_Null; } } public static DistanceCs Null { get { DistanceCs h = new DistanceCs(); h.m_Null = true; return h; } } public static DistanceCs Parse(SqlString s) { if (s.IsNull) return Null; DistanceCs u = new DistanceCs(); string distance = s.Value; if (distance == "null") return Null; distance = distance.ToLower(); int feet = 0; int inches = 0; string[] parts = distance.Split(' '); int feetLocation = Array.IndexOf(parts, "ft."); if (feetLocation > 0) { feet = int.Parse(parts[feetLocation-1]); } int inchesLocation = Array.IndexOf(parts,"in."); if (inchesLocation > 0) { inches = int.Parse(parts[inchesLocation - 1]); } u.totalInches = (feet * 12) + inches; return u; } public int CompareTo(object obj) { DistanceCs other = (DistanceCs)obj; return totalInches - other.totalInches; } private int totalInches; private bool m_Null; }
Notice the use of the SqlUserDefinedType attribute, which tells Visual Studio .NET how to deploy this structure. If you need to manually register a UDT with SQL Server, you can use the following SQL syntax.
The IComparable interface was also implemented on this UDT, which was not a UDT requirement, but you will want the ability to display and sort this UDT in a DataGridView control. For this, the IComparable must be implemented.
SQL: UDT Registration
CREATE TYPE <UDT name> FROM <assembly name>.<UDT name>
The only data being persisted is totalInches, followed by m_Null. This means it is easy to provide sorting and comparisons based on the byte representation. Read-only properties were created to access the feet and inches independently. These properties are available in your .NET code as well as your T-SQL statements.
This sample also implements the INullable interface methods, IsNull and Null. Anywhere that a null is required for this data type, the Null method is used to generate it.
To test this UDT, add the following SQL script to the Test.sql file.
Visual Basic
create table UdtTestVb (Id int not null, distance distanceVb not null) insert into UdtTestVb values(1, '2 ft. 5 in.') insert into UdtTestVb values(2, '15 in.') insert into UdtTestVb values(3, '10 ft.') insert into UdtTestVb values(4, '1 ft. 23 in.') select id, convert(nvarchar(25),distance) from UdtTestVb drop table UdtTestVb
C#
create table UdtTestCs (Id int not null, distance distanceCs not null) insert into UdtTestCs values(1, '2 ft. 5 in.') insert into UdtTestCs values(2, '15 in.') insert into UdtTestCs values(3, '10 ft.') insert into UdtTestCs values(4, '1 ft. 23 in.') select id, convert(nvarchar(25),distance) from UdtTestCs drop table UdtTestCs
Output Window: UDT
id Column1 ----------- ------------------------- 1 2 ft. 5 in. 2 1 ft. 3 in. 3 10 ft. 0 in. 4 2 ft. 11 in. (8 row(s) affected) (4 row(s) returned)
You should never use a UDT to model business objects such as customers, employees, orders, or products. Remember that in a relational database, these objects are usually represented by rows in one or more tables, whereas the UDT is represented as a single value in a single column. The 8000-byte limit is much higher than you should ever need. Indexing, sorting, and magnitude comparisons are done only one way, across the entire binary representation. This is workable if the UDT is truly atomic, but if you need different indexes, the UDT is not for you.
Any operation on a UDT except comparisons causes the UDT value to be deserialized and a method to be invoked. You should therefore consider performance implications when evaluating the use of a UDT.
If you have atomic data, typically represented as a single field, and you have many complex methods that need to be associated with the data, the UDT might be the answer.
Also, it's worth noting that if you simply want to create a library of functions for use in T-SQL, you can create a type that has no data and comprises static methods. This is equivalent to creating user-defined functions, but the benefit is that these methods are grouped inside your type. Beware that you cannot assign execute permissions to the methods on a UDT. The following code shows how you can package a PadLeft and PadRight method into a UDT.
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _ Public Structure StringStuffVb Implements INullable Public Shared Function PadLeft( _ ByVal inputString As SqlString, _ ByVal totalWidth As SqlInt32) As SqlString Return New SqlString(_ inputString.Value.PadLeft(totalWidth.Value)) End Function Public Shared Function PadRight(_ ByVal inputString As SqlString, _ ByVal totalWidth As SqlInt32) As SqlString Return New SqlString(_ inputString.Value.PadRight(totalWidth.Value)) End Function Public Overrides Function ToString() As String ' Put your code here Return "" End Function Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull Get ' Put your code here Return m_Null End Get End Property Public Shared ReadOnly Property Null() As StringStuffVb Get Dim h As StringStuffVb = New StringStuffVb h.m_Null = True Return h End Get End Property Public Shared Function Parse(ByVal s As SqlString) As StringStuffVb If s.IsNull Then Return Null End If Dim u As StringStuffVb = New StringStuffVb ' Put your code here Return u End Function ' Private member Private m_Null As Boolean End Structure
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct StringStuffCs : INullable { public static SqlString PadLeft( SqlString inputString, SqlInt32 totalWidth) { return new SqlString( inputString.Value.PadLeft(totalWidth.Value)); } public static SqlString PadRight( SqlString inputString, SqlInt32 totalWidth) { return new SqlString( inputString.Value.PadRight(totalWidth.Value)); } public override string ToString() { // Replace the following code with your code return ""; } public bool IsNull { get { // Put your code here return m_Null; } } public static StringStuffCs Null { get { StringStuffCs h = new StringStuffCs(); h.m_Null = true; return h; } } public static StringStuffCs Parse(SqlString s) { if (s.IsNull) return Null; StringStuffCs u = new StringStuffCs(); // Put your code here return u; } // Private member private bool m_Null; }
The UDT in this code sample has no fields for user data. The PadLeft and PadRight methods are added as static (Visual Basic shared) methods. The trick to using these methods in a T-SQL statement is to know the calling syntax. Here is the code you can add to your Test.sql file.
Visual Basic
select '<' + StringStuffVb::PadLeft('Hi',10) + '>' select '<' + StringStuffVb::PadRight('Hi',10) + '>'
C#
select '<' + StringStuffCs::PadLeft('Hi',10) + '>' select '<' + StringStuffCs::PadRight('Hi',10) + '>'
The output looks like this:
Column1 ----------------------- < Hi> (8 row(s) affected) (1 row(s) returned) Column1 ----------------------- <Hi > (8 row(s) affected) (1 row(s) returned)
Note that the affected value of the row(s) may differ based on where you placed this T-SQL script command in your test file. In reality, no rows were affected by this T-SQL script, but the number was carried down from a previous T-SQL command.