SHOULD OBJECTS BE REPRESENTED BY USER-DEFINED scalar. Note

SHOULD OBJECTS BE REPRESENTED BY USER-DEFINED scalar. Note that the number of fields required to represent a value does not determine whether or not it is a scalar; we saw the date example at the beginning of this chapter that had three fields but was still a scalar. Is a geometric point a scalar? No, because its representation requires two dimensions, not just two numbers. But why is a date that has three numbers a scalar but a point that has only two numbers associated with it not a scalar? You can ask of any date something like Is date d2 between date d1 and date d3? and get a consistent answer. But there is no way in general to consistently answer the question Is point p2 between point p1 and point p3? The reason you can t get a consistent answer to this question for points is that each answer to a between question makes a commitment to the next. For example, consider the following questions and answers. Is date d2 between date d1 and date d3? Yes. Is date d4 between date d1 and date d2? Yes. If someone then asks, Is date d4 between date d1 and date d3? the answer must be yes. This is because dates have an order they in effect map onto the real number line. In fact, internally SQL Server can use floating point numbers to represent dates because of this. This is not true for points, because they are not scalars. One of the problems with asking a between question of points is that our conventional notion of between would require all three of the points involved to be on the same line. If we use this kind of definition of between for points, there are many points for which we cannot answer the between question, because it makes no sense. But if we can just come up with an alternate definition of between that works consistently, we can still consider points to be scalars. Let s try to make up something that is similar to the way between works for dates. For dates, we can say that d2 is between d1 and d3 if both abs(d2 -d1) Java Web Hosting services

USER-DEFINED TYPES AND AGGREGATES and so on references

USER-DEFINED TYPES AND AGGREGATES and so on references it. All UDTs, by definition, support conversion to and from a string, so DTS will work with any UDT. This is safest because it guarantees that any changes to the semantics of the type and how it is represented will be propagated to the entire database. Note that it may not be possible to reimport some of the data because the new type definition may not allow it. It will, however, guarantee the integrity of the database, which is paramount in any change. In many cases, you will not be changing something as fundamental as the semantics of the type or how it is represented. In these cases, you can use the ALTER ASSEMBLY command, described in Chapter 2. The ALTER ASSEMBLY can replace an existing assembly with user-defined types in it without dropping objects such as tables and stored procedures that reference those types. ALTER ASSEMBLY is meant to be used to make bug changes or improve implementations, not semantic or operational changes. For instance, you cannot use ALTER ASSEMBLY to replace an assembly with one whose assembly name is different in anything other than the revision of the assembly. Note that the revision is the fourth of the dotted list of numbers that specifies an assembly version. In the assembly version 1.2.3.4, 4 is the revision of the assembly. It is your responsibility, however, when changing an assembly this way, to ensure that the meaning, sorting, and so on of any data persisted by user-defined types is not changed by replacing the assembly. The ALTER ASSEMBLYcan check for some of these issues, but not all. Keep this in mind when deciding between drop and re-create, and ALTER ASSEMBLY. Maintaining integrity of data should always be the most important factor when you are making this decision. Should Objects Be Represented by User-Defined Types? If the object represents a scalar and requires more than one field to be described, a user-defined type should be used to represent it; otherwise, it should not. By definition, a column in a row of a relational table holds a scalar, so it can hold any object that is a scalar. This definition is crucial to features we associate with a relational database such as the ability to have efficient declarative referential integrity via foreign keys and to order rows when they are used rather than when they are inserted. A scalar is a value that can be represented in a single dimension. A value that requires more than one dimension to be represented is not a

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

IMPLEMENTING LDIM interface, or a Mapfunction (a function

IMPLEMENTING LDIM interface, or a Mapfunction (a function that takes a UDT instance and returns an orderable scalar value). SQL Server UDTs only sup port ordering by state values. Note that this is done, as explained previously, only if the SqlUserDefinedAttributeproperty ByteOrderedis truefor the class that implements the user- defined type. XML Serialization The FOR XML clause of a SELECT statement that includes a user-defined type and DataSetsthat include user-defined types depend on those types being XML serializable. Though a user-defined type is not required to be XML serializable, if it is not, it may produce errors or corrupt data when used with FORXMLand DataSets. It is best practice to ensure that the class you implement is XML serializable. An explanation of the requirements for XML serializability is beyond the scope of this book. Typically, it is accomplished by having the class that implements the user-defined type also implement the IXmlSerializable interface. Refer to the XmlSerializer class in MSDN for details of XML serialization. Maintaining User-Defined Type Definitions There are two ways to replace a user-defined type. One is to use DROP TYPE followed by CREATE TYPE. The other is to use the ALTER ASSEMBLY command to replace the assembly that contains the implementation of the user-defined type. Changing a user-defined type is a fundamental change to a database. Think about how you would feel if Microsoft said they were going to change how the SQLDECIMALdata type worked, even if they said it would just improve the accuracy of decimal. The fact that calculations you had done in the past might produce different, albeit more accurate, results could easily have an overwhelming impact on your database because things that compared as equal in the past might not do so after the change. You must keep this level of impact in mind whenever you make a change to a user-defined type. The safest way to change user-defined type is to export tables that refer to the type to a text file using SQL Server Data Transformation Services (DTS), drop and re-create the type and everything that references it, and then reload the tables. You will not miss anything that refers to the user- defined type, because you cannot drop a type if any table, stored procedure,

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

USER-DEFINED TYPES AND AGGREGATES return Parse(value.Value.ToString() +

USER-DEFINED TYPES AND AGGREGATES return Parse(value.Value.ToString() + + units.Value.ToString()); } } The MakeLDim function takes a double and a string as input. If either is null, it returns a null instance of an LDim. Otherwise, it builds a string and passes it to the Parse method. This will check the units passed into MakeLDim, and, if they are correct, create an instance of an LDim with the appropriate value. This convenience function makes the parametric insertion of an LDimmuch easier, as is shown in the SQL batch in Listing 5-24. Listing 5-24: Parametric Insertion Using MakeLDim DECLARE @d AS LDim CREATE TABLE Dimensions ( dim LDim ) go parametric value DECLARE @v AS FLOAT DECLARE @u as VARCHAR(2) SET @v = 1 SET @u = yd INSERT INTO Dimensions VALUES (dbo.MakeLDim(@v, @u)) As a result of having the MakeLDimfunction, the code that inserts a new LDimwith parameters for its value is much easier to both write and read. Unsupported Functions in User-Defined Types There are a few types of functionality that, while currently specified in SQL:1999, are unsupported in SQL Server SQL Server 2005 s implementation of UDTs. It s possible that the SQL Server team will add some of these over time as the utility of UDTs becomes more apparent. These functions include the following: Overloaded methods. Inheritance exposed at the Transact-SQL level. Exposing SQL properties such as numeric precision and scale in a UDT. Support of ordering. SQL:1999 specifies that UDTs can support ordering based on state values, implementation of a specific

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 LDIM SqlFunctionAttributeproperties (such as DataAccessKind) can be

IMPLEMENTING LDIM SqlFunctionAttributeproperties (such as DataAccessKind) can be used to declare the behavior of UDT methods. Chapter 3 discusses how to create stored procedures, functions, and triggers using a CLR language such as C#. It is sometime useful to create convenience functions for use with user-defined types. This is can be problematic when parametric values must be used to insert an instance of a user-defined type. This is shown in the SQL batch in Listing 5-22. Listing 5-22: Parametric Insertion of an LDim DECLARE @d AS LDim CREATE TABLE Dimensions ( dim LDim ) GO parametric value DECLARE @v AS FLOAT DECLARE @u AS VARCHAR(2) SET @v = 1 SET @u = yd INSERT INTO VALUES (Convert(varchar, @v) + + @u) The SQL batch in Listing 5-22 inserts 1 yd into the Dimensions table. To do this, it had to construct a string that could be inserted into an LDim column. This is an example of a case where a convenience function that can produce an instance of a user-defined type would be very useful. Note that a convenience function is not an instance member of the class that implements the user-defined type and does not use the SqlMethod Attributeattribute; it is a static member of it. In fact, it is only for keeping it together with the user-defined type definition; it could be part of a completely different class. Listing 5-23 shows the implementation of the Make LDimString method. Note also that this method will have to be added to the database that uses it by using the CREATE FUNCTION command, as described in Chapter 3. Listing 5-23: Implementation of the MakeLDimString Method public static LDim MakeLDim(SqlDouble value, SqlString units) { if (value.IsNull || units.IsNull) { return new LDim(); }

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 The SqlMethodAttribute provides SQL

USER-DEFINED TYPES AND AGGREGATES The SqlMethodAttribute provides SQL Server with information about how the method will interact with SQL Server. Some methods marked with SqlMethodwill change the value of the object on which they are called. This is true for the SetValue method. Since the SetValue changes an LDimobject, it must be used with a setsyntax, as shown in the SQL batch in Listing 5-20. Listing 5-20: Using the LDim SetValue Method DECLARE @d AS LDim SET @d.SetValue(2.3, ft ) PRINT CONVERT(CHAR, @d) 2.3 ft If the IsMutator property of the SqlMethod had been set to false, the SQL batch shown in Listing 5-20 would have produced an error when executed. Methods that have IsMutator = false can be used in VIEWS and SELECTstatements but not in UPDATEstatements. The SqlMethod has another property, named OnNullCall, which affects its behavior when a null parameter is passed in. You can think of OnNullCall=true as meaning Call this method even if a null is passed as a parameter. If OnNullCall=false, the method will not be called, and the instance of the user-defined type will be set to null. This property is set to false on the SetValue method. This means that when a null is passed to it, the LDimwill be set to null. That is why the implementation of the SetValue method does not have to check whether any of the input parameters are null. The SQL batch in Listing 5-21 shows that SetValueis not called when a nullis passed in. Listing 5-21: Passing a Null to SetValue DECLARE @d AS LDim SET @d.SetValue(2.3, ft ) PRINT CONVERT(CHAR, @d) 2.3 ft SET @d.SetValue(null, ft ) PRINT CONVERT(CHAR, @d) null The beginning of the SQL batch in Listing 5-21 sets @d to 2.3 ft and then prints it out. It then attempts to set it to null ft. It then prints out @d and shows that it has, in fact, been set to null. In addition, because SqlMethodAttribute derives from SqlFunctionAttribute, any of the

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

IMPLEMENTING LDIM } if(((units == null) or (units

IMPLEMENTING LDIM } if(((units == null) or (units == )) && (value != 0)) { return false; } return true; |} The CheckRep function checks to make sure that value is an actual number. SQL Server does not have a representation for either NaN or infinity, so value could never be either of these. It also checks to see that the value is 0 if there are no units. So if somehow, by directly inserting binary into an LDim, LDimhad an invalid binary representation, SQL Server could detect it. Note that the CheckRep function is private. The only requirements of the method indicated by the ValidationMethodName are that it return a booland take no arguments. Its scope can be private, internal, or public. User-Defined Type Methods Public methods in a class that implements a user-defined type can be exposed to SQL Server. The method must be marked with the SqlMethod Attribute, and its parameters and return value must be compatible with the types that SQL Server supports. Listing 5-19 shows an example of a method in the LDimimplementation that can be used to set it. Listing 5-19: Method That Sets the Value of an LDim namespace UDT { public class LDim { // marks method to be exposed to SQL Server [SqlMethod(IsMutator=true, OnNullCall=false)] // method to set the value of an LDIM public void SetValue(SqlDouble value, SqlString units) { if (units == in || units == ft || units == yd ) { this.units = units.Value; this.value = value.Value; } } } }

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 could result in an

USER-DEFINED TYPES AND AGGREGATES could result in an invalid representation. For example, look at the following SQL batch that inserts a binary number into an LDimcolumn. Note that binary numbers are implicitly converted to a user-defined type. CREATE TABLE Dims (data LDim) INSERT INTO Dims VALUES (0xBFFFFFFFFFFFFFFE000000000000F03F01) SELECT data FROM Dims 1 in The binary number, 0xBFF … 3F01, is the binary representation of 1in . The very last byte, the 01 on the right, enumerates the units of the dimension. 1 indicates inches, 2 feet, and 3 yards. What happens if that last number is a 4, so that 0xBFFF…3F04 is inserted? Nothing in the representation expects a 4 to be there, and there is no way for the LDim user-defined type to detect that the binary number was inserted. What happened is that a very subtle error was injected into the data, which may be detected at some later time or may just go on to corrupt other data. The SqlUserDefinedTypeAttributehas a property that allows you to specify a method that SQL Server will use to validate an instance of a user- defined type no matter how that value was set. The ValidationMethod Name property takes a string as a parameter and specifies a method in the implementation of the user-defined type that SQL Server can call to validate the binary representation of an instance of the user-defined type. For example, we can extend the implementation of LDimas follows. [Serializable] [SqlUserDefinedType( Format = Format.Native, IsByteOrdered = true, IsFixedLength = true, MaxByteSize = 17, ValidationMethodName= CheckRep )] public struct LDim: INullable, IBinarySerialize { // rest of implementation bool CheckRep() { if(double.IsInfinity(value) || double.IsNaN(value) { return false;

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

IMPLEMENTING LDIM get { return units; } //

IMPLEMENTING LDIM get { return units; } // set must validate set { // if null is passed in make it null if (value.IsNull) { SetNull(); return; } // if valid unit is passed in then change // unit and adjust value to match if (value == in || value == ft || value == yd ) { double curFactor = Factor(units); double newFactor = Factor(value.Value); units = value.Value; this.value *= curFactor / newFactor; return; } // if you get to here an // invalid unit was passed in so throw an exception throw new ApplicationException(value.Value + Is not valid unit , null); } } } This code has a Unit property that wraps the units field in LDim. The set portion of the property validates any change to the units field and throws an exception when an attempt is made to set units to an unsupported value. Note that this code is much more complex than just exposing a field. However, any code you add to SQL Server 2005 must protect the integrity of the database no matter what. The get part of the property is straightforward; it just returns the current value of units. The set part of the property must do two things. One is to check that a valid unit string is being used. The other is to convert the current value of the LDim to the value in the new units. This ensures that if the value of an LDim is 1 ft and the units are changed to in , the value is changed to 12. User-Defined Type Validation It is possible to insert a user-defined type value into a column without using the Parse method implemented in the user-defined type. This is done by just inserting a binary number directly into the column. Doing this

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

USER-DEFINED TYPES AND AGGREGATES Listing 5-16: Exposing the

USER-DEFINED TYPES AND AGGREGATES Listing 5-16: Exposing the units Field of LDim public struct LDim : INullable { // public field can be accessed by SQL Server public string units; // rest of implementation } Listing 5-17 shows how the unitsfield can be accessed in a SQL batch. Listing 5-17: Accessing a Field of a User-Defined Type DECLARE @d AS LDim SET @d = CONVERT(LDim, 1 ft ) PRINT @d.units ft SET @d.units= miles The example shown in Listing 5-17 declares an LDim variable, sets its value to 1 ft , and then prints out the unit ft . It then sets the unitsfield to miles . Does this make sense? Miles is not one of the units that LDim supports. This implementation of LDimshows one of the traps into which it is easy to fall when exposing fields of the implementation of a user-defined type; this implementation is guaranteed to lead to corrupted data. The best practice is to never directly expose the fields of the implementation of a user-defined type, but to instead wrap those fields in a property so that you can validate any change made to that field. Listing 5-18 shows a best practice implementation of exposing the unitsfield of an LDim. Listing 5-18: Exposing units of LDim as a Property // used to recalculate value when units change private static double Factor(string units) { if (units == in ) return 1.0; if (units == ft ) return 12.0; if (units == yd ) return 36.0; return 1.0; } // property that wraps units in a SqlString public SqlString Units { // return is simple

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