IMPLEMENTING LDIM Implementing INullable INullable is an interface

IMPLEMENTING LDIM Implementing INullable INullable is an interface defined in the System.Data.Sql namespace. Implementing it is straightforward; it has a single, read-only property named IsNull that returns a System.Boolean (bool in C# or Boolean in VB.NET). Some thought has to be put into how a user-defined type will internally represent a null value. A simple way to do this is to add a field that stores a flag to indicate whether or not the value is null. This is shown in Listing 5-5. Listing 5-5: Simple Implementation of INullable public struct LDim : INullable { private double value; private string units; private bool valueNotNull; // read-only property public bool IsNull { get { return !valueNotNull;} } // rest of implementation } This implementation of IsNull is taking advantage of the fact that the fields of a struct are always initialized to zero. This means that the parameterless constructor for LDimwill be initialized to be a null value. That is the reason for the double negative that appears in the IsNull property implementation. This way of implementing IsNullhas costs in space. One cost is in the memory taken up by the valueNotNull field, but in many cases this may be of no significance. The other cost is in the space taken up on the disk to store an instance of an LDim type, which will be multiplied by the number of rows in a database that contain LDim columns. There are two ways to mitigate this. If the class implements IBinarySerialize, it can choose a more compact way to save a null instance. For convenience, this implementation ensures that when a new LDimis created using new LDim(), it will in fact be a null representation. Because of this, the test for null requires a double negative. The other way is to encode the null representation in the existing fields of LDim. This will make it easier to implement IBinarySerialize, which

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

USER-DEFINED TYPES AND AGGREGATES double seconds; // third

USER-DEFINED TYPES AND AGGREGATES double seconds; // third sort key byte direction; // 0 for E, 1 for W, fourth sort key public override string ToString() { } public bool IsNull { } public static LDim Null { } public static LDim Parse(SqlString s) { } } Implementing LDim We will now look at implementing the LDim user-defined type, shown in Listing 5-4. It will illustrate most, though not all, of the issues involved in implementing user-defined types. LDimis a user-defined type that is used to represent a linear dimension. It has two properties associated with it: value and units. The units are used to scale the value so that, for example, 1 ft is the same value as 12 in . This sort of data type is widely used in various engineering, architecture, and other disciplines. The basic implementation of LDim is shown in Listing 5-4. Listing 5-4: Basic Implementation of LDim public struct LDim { private double value; private string units; // rest of implementation } It is a requirement that a public CLR class must be used to implement a user-defined type. Note that in C# and VB.NET, a struct is a CLR class that is derived from System.ValueType. There are number of trade-offs to be made in choosing a class that derives from System.ValueTypeand one that doesn t. These are discussed in MSDN and other places. This basic implementation of LDimcan maintain the state of a dimension.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

IMPLEMENTING A USER-DEFINED TYPE public struct LDim: INullable,

IMPLEMENTING A USER-DEFINED TYPE public struct LDim: INullable, IBinarySerialize { double value; string units; public override string ToString() { } public bool IsNull { } public static LDim Null { } public static LDim Parse(SqlString s) { } public void Write(System.IO.BinaryWriter w) { } public void Read(System.IO.BinaryReader r) { } } Note that LDim sets the Format property of SqlUserDefinedType to UserDefined. Also note that the LDim field units is a string. Because of this, LDimmust be implemented with UserDefined. Note that best practice in this case would be to use an int as the type for units, but the purpose of this example is to cover as many of the implantation details of a user- defined type as possible. Listing 5-3 shows a skeleton implementation of a user-defined type for Longitude. This user-defined type sets the Format property of SqlUserDefinedType to Native. Note that implementation of this type will be similar to that for LDim, except that the Write and Read methods are not required. Listing 5-3: Skeleton Implementation of Longitude [Serializable] [SqlUserDefinedType( Format = Format.Native, IsByteOrdered = true )] public struct LDim: INullable { double degrees; // first sort key double minutes; // second sort key

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

USER-DEFINED TYPES AND AGGREGATES Table 5-1: Requirements for

USER-DEFINED TYPES AND AGGREGATES Table 5-1: Requirements for User-Defined Type Implementation Form at INullableIBinarySerializableMaxBytesIsFixedLengthIsByteOrderedNull PropertyParse MethodReference TypesToString Method[Serializable] [StructLayout] UserDefined Y Y Y Y Y Y Y Y Y Y N Native Y N N N Y Y Y Y Y Y Y All classes that implement a user-defined type must be marked with the [Serializable] attribute. Regardless of the setting of the Format property, the maximum size of the byte stream used to save an instance of a user-defined type is 8,000 bytes. Any class used to implement a user-defined type must also implement the INullable interface, override the System.Object.ToString method, and implement a static read-only property named Null and a static method named Parse. Table 5-1 summarizes the requirements for classes used to implement a user-defined type. The requirements vary depending on whether Format.UserDefined or Format.Native is used. You can use this as a checklist when implementing a user-defined type. The first column on the left lists the two possible values for the Format property of the SqlUser DefinedTypeAttribute. The remaining columns show whether or not a particular feature is required for the implementation. For example, the IsFixedLengthproperty of the SqlUserDefinedTypeAttributeattribute is required when Format.UserDefined is used, but is not required when Format.Nativeis used. Listing 5-2 shows the skeleton implementation for the user-defined type we wish to create, LDim. Listing 5-2: Skeleton Implementation of LDim [Serializable] [SqlUserDefinedType( Format = Format.UserDefined, IsByteOrdered = true, IsFixedLength = true, MaxByteSize = 17 )]

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

IMPLEMENTING A USER-DEFINED TYPE user-defined type could be

IMPLEMENTING A USER-DEFINED TYPE user-defined type could be used to represent a nail that will be sorted first on its Sizefield and second on its Pricefield. namespace UDT { [SqlUserDefinedType( Format=Format.Native, // SQL Server provides serialization code IsByteOrdered = true)] // Sorts and compares may be used [Serializable] // required for Native [StructLayout(LayoutKind.Sequential)] // required for Native public struct Nail … { int Size; // sort on Size first float Price // sort on Price second // implementation … } } Format = Format.Native is best practice when it can be used. There are two simple cases when it cannot be used. One is when the class requires a field of a type that is not allowed for Format.Native. The other is that you cannot achieve the sort order that you wish by just ordering the fields in the CLR class. A class that can t meet the requirements to use Native must use System.Data.Sql.Format.UserDefined. When Format is set to User Defined, the class implementing the user-defined type must provide the code to read and write the byte stream by implementing the IBinary Serialize interface. In addition, the UserDefined setting requires two other properties of SqlUserDefinedTypeAttribute to be set appropriately: the IsFixedLengthand MaxByteSizeproperties. The MaxByteSize property is used only when Format = Format. UserDefinedand indicates the maximum size of the byte stream, in bytes, used to save the user-defined type. It is desirable to keep this as small as possible. You will have to accurately determine this when you implement the IBinarySerializeinterface, which will be covered later. The IsFixedLength property is used only when Format = Format. UserDefined and is set to true if the size of the byte stream is always the same size as indicated by MaxByteSize; otherwise, it is set to false. SQL Server needs to know this because the way it allocates space on the disk differs depending on whether its size is fixed or not. In general, SQL Server can more efficiently manage a fixed size type better than one whose size varies.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

USER-DEFINED TYPES AND AGGREGATES SqUInt16, SqlInt32, SqlUInt32, SqlInt64,

USER-DEFINED TYPES AND AGGREGATES SqUInt16, SqlInt32, SqlUInt32, SqlInt64, SqlUInt64, and SqlDate Time, even though the MSDN documentation does not list them as being blittable. Note that System.Char, SqlDate, SqlBytes, SqlDecimal, and SqlBinary cannot be used as a field of a user-defined type that uses Format=Format.Native. Note that both the documentation and the runtime are beta implementations, so the types useable by Format.Native may be different by the time the final version ships. A code fragment of a CLR class definition that could use Format=Format.Nativefollows. namespace UDT { [Serializable] [StructLayout[LayoutKind.Sequential)] public struct Position { public int X; public int Y; } [SqlUserDefinedType( Format=Format.Native, // SQL Server provides serialization code IsByteOrdered = true)] // Sorts and compares may be used [Serializable] // required for Native [StructLayout(LayoutKind.Sequential)] // required for Native public struct Contract … { Position Location; SqlDateTime created; float Price; int LineCount; // implementation … } } A user-defined type, whether it uses Format.Native or Format. UserDefined, can be sorted, used in an index, or used in a magnitude comparison only if the IsByteOrdered property of the SqlUserDefined TypeAttribute is set to true. This has implications on its implementation, which will be discussed later. Sorting and magnitude comparisons of user-defined types are always done directly using the byte stream that is stored on disk, regardless of whether SqlUserDefinedTypeAttributeuses Format.Nativeor Format. UserDefined. In the case of Format.Native, the sort order will be determined by the order in which the fields of the CLR class definition are defined. The first field defined will be, in effect, the first sort key, the second field defined the second, and so on. The following code fragment of a

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

IMPLEMENTING A USER-DEFINED TYPE Instances of user-defined types,

IMPLEMENTING A USER-DEFINED TYPE Instances of user-defined types, just like any other scalar type, are saved to and read from disk as a stream of bytes. For all user-defined types, this stream of bytes can be no larger than 8,000 bytes. SQL Server needs to know how much space is required on disk for an instance of a user-defined type and what code to use to read and write the byte stream that is saved on the disk. SQL Server also needs to know whether or not it can be ordered according to the byte stream that is stored on disk for it. The SqlUserDefinedTypeAttributeused on a CLR class definition for a user- defined type provides this information. This is shown in the following code fragment. namespace UDT { // properties of SqlUserDefinedType describe to SQL Server // how to save and order an LDim [SqlUserDefinedType(...)] public struct LDim … { // implementation … } } The SqlUserDefinedTypeAttribute has a property named Format. When the Format property is set to System.Data.Sql.Format.Native, SQL Server provides the code that is used to read and write the byte stream. There are a number of requirements of a class that uses the Native setting of the Format attribute the class must use the StructLayout Attribute with its Value property set to LayoutKind.Sequential; all fields in the CLR class must be blittable; and the class must have a SerializableAttribute attribute. For example, a class that has a field of type string may not have the Format property of the SqlUserDefined TypeAttributeset to Native. The MSDN documentation enumerates the CLR classes that are blittable. In essence, only value types that have the same physical representation in the CLR as they do in the native operating system are blittable. The MSDN documentation specifically lists System.Byte, System.SByte, System.Int16, System.UInt16, System.Int32, System.UInt32, System. Int64, System.UInt64, System.IntPtr, System.UIntPtr, and any one- dimensional array of the types previously listed as being blittable. In addition to these, a Format.Native user-defined type may also use System. Float, System.Double, SqlSingle, SqlDouble, SqlByte, SqlInt16,

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

USER-DEFINED TYPES AND AGGREGATES There are two steps

USER-DEFINED TYPES AND AGGREGATES There are two steps in creating a user-defined type. The first is to create an assembly that contains the implementation of the user-defined type. This can be done using any language that the CLR supports. The second step is to add the assembly and the user-defined type to a database. The assembly is added to the database using the CREATE ASSEMBLY command, as was shown in Chapter 2. The user-defined type can then be added to the database by the CREATETYPEcommand, as shown in Listing 5-1. Note that user-defined types, unlike the built-in scalar types that SQL Server provides, must be specifically added to every database that requires them. Implementing a User-Defined Type The implementation of a user-defined type is a CLR class. The CLR classes are divided between reference types and value types. The principle difference between these two kinds of classes is how memory for instances of them is managed. The memory allocated for a reference type is always a pointer to a sequence of bits that is, a pointer to an object. The memory allocated for a value type is a sequence of bits that is, the object itself. There is no difference between these kinds of classes in the treatment of methods. Operationally, the semantics of parameter passing is different between them, and depending on usage, one may have a greater impact on performance or resource usage. Both the MSDN documentation and the Common Language Infrastructure Partition I discuss the differences between these. Either can be used to implement a user-defined type. Both C# and VB.NET use the classkeyword to define a CLR reference type class. C# uses the struct keyword, and VB.NET uses the Structure keyword to define the CLR value type class. We will use a structto implement LDim. The following code is a skeleton of the CLR class that we will use to implement LDim. This skeleton meets the basic requirement that a user-defined type be a CLR class. namespace UDT { // struct is used to define a CLR value type class public struct LDim … { // implementation … } }

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

CREATING A USER-DEFINED TYPE Find the average

CREATING A USER-DEFINED TYPE Find the average length and the sum of the widths of all of the tiles SELECT CONVERT(CHAR, dbo.LDimAvg(Length)) as Avg, CONVERT(CHAR, dbo.LDimMax(Length)) as Sum FROM Tiles GO 30.2 in 36 in CREATE ASSEMBLY, as explained in Chapter 2, adds the assembly that contains the user-defined type. CREATE TYPE is used to add a user-defined type to a database. CREATE TYPE is followed by the name that you will use to refer to the user-defined type in SQL Server. In this case, we will refer to the type as LDim. CREATE TYPE also has an EXTERNAL NAME that is used to reference the actual implementation of the user-defined type. The EXTERNALNAME is broken into two parts separated by a period. The first part is the name of the assembly that contains the implementation. LDim is the name of the assembly that was previously loaded into the database using the CREATE ASSEMBLYcommand. The second part of the EXTERNAL NAME is the name of the class, in the assembly, that actually implements the user-defined type. In this case, the class is UDT.LDim, because our LDim implementation is in namespace UDT. Note that the namespace name and the class name are enclosed in square brackets. This is required to distinguish the . that separates the name- space from the class name, from the . that separates the assembly name from the class name. If no namespace was involved, the square brackets would not be required. Note the casing of the namespace and class names. These must match those used in the implementation. For case-sensitive languages like C#, this is not an issue. For case-insensitive languages like VB.NET, the casing must match that used in the definition of the class. So, you can see that LDim acts the same way as any other scalar variable. It can be sorted, compared, and, with the help of LDim-specific aggregation functions, aggregated. Creating a User-Defined Type We will now look at how to create a user-defined type. We will create a type meant to represent a linear dimension. It has two parts: value and units. The value is a double. The units can be one of in for inches, ft for feet, and yd for yard.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

USER-DEFINED TYPES AND AGGREGATES SQL Server provides a

USER-DEFINED TYPES AND AGGREGATES SQL Server provides a number of aggregate functions, such as MAXand AVG, for many of its scalar types. Likewise, in SQL Server 2005 you can create user-defined aggregates for the user-defined types that you create. To illustrate a user-defined type, we will implement one that supports a simple linear dimension. It is called LDim. An instance of this type will contain a numeric value and units. The units can be any of in , ft , or yd . A typical LDimwe might write would look like 1 ft or 7 yd . We want to be able to treat LDimas we would any other scalar variable, so we will also implement the aggregate functions LDimAvgand LDimMaxfor it. Listing 5-1 shows a SQL batch that uses LDim. Listing 5-1: Adding and Using a User-Defined Type First we catalog an assembly we have previously built that contains the implementation of LDim This chapter is about how to make this assembly CREATE ASSEMBLY LDim FROM c:userTypesbinLDim.dll GO Next we add LDim to the scalar type system CREATE TYPE LDim EXTERNAL NAME LDim.[UDT.LDim] GO Now we can use LDim just like any other scalar type Create a table that contains rectangular tiles, using LDim for the dimensions of the tile CREATE TABLE Tiles ( Id IDENTITY PRIMARY KEY, Length LDim, Width LDim ) GO add some tiles to the table INSERT INTO Tiles VALUES (N 1 ft , N 6 in ) INSERT INTO Tiles VALUES (N 2 yd , N 2 in ) INSERT INTO Tiles VALUES (N 7 in , N 1 in ) INSERT INTO Tiles VALUES (N 2 ft , N 3 in ) INSERT INTO Tiles VALUES (NULL, NULL) find all the tiles that are greater than 18 in long SELECT Id FROM Tiles WHERE Length > N 18 in GO 2 order tiles by their width SELECT Id from Tiles ORDER BY Width go 3 2 4 1 5

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services