A user-defined aggregate (UDA) allows you to define an aggregate function that works like the AVG, SUM, MIN, MAX, and COUNT functions described in chapter “Coding Summary Queries“. Then, you can use your aggregate function in summary queries just like the aggregate functions provided by SQL. For the sake of brevity, aggregate functions are often […]
Read More...How to work with the SqlUserDefinedAggregate attribute
Table below shows how to work with five of the most commonly used properties of the SqlUserDefinedAggregate attribute. The Format property must be coded first in the list of properties, and it’s specified using one of the members of the Format enumeration. This property defines the serialization format that’s used for the aggregate. If the […]
Read More...An aggregate that returns a trimmed average
Code below shows how to code an aggregate that works much like the AVG function provided by SQL. However, the aggregate shown here creates a “trimmed” average by not including the highest and lowest values when it calculates the average. This type of aggregate might be useful if you have a set of values where […]
Read More...An aggregate that returns a comma-delimited string
Code below shows how to code an aggregate that returns a comma-delimited string. This aggregate uses a field that’s instantiated from the StringBuilder class, which is a reference type. As a result, it can’t use the Native serialization format. Instead, the SqlUserDefinedAggregate attribute specifies that this aggregate uses the UserDefined serialization format. Since this aggregate […]
Read More...How to work with & declare user-defined types
If you’ve gone through the chapter “How to work with XML“, you know that the xml data type includes methods that provide functionality that isn’t available from most SQL Server data types. Similarly, when you create a user-defined type (UDY), you can define properties and methods that let you work with the data that’s stored […]
Read More...How to work with the SqlUserDefinedType attribute
First Table below shows how to work with four of the most commonly used properties of the SqlUserDefinedType attribute. To start, the Format property works just as it does with the SqlUserDefinedAggregate attribute: It is required, it must be coded first in the list of properties, and it is specified using the Format enumeration. Since […]
Read More...A user-defined type for an email address
Code below shows an example of a user-defined type named Email that defines a data type for an email address. This UDT provides several advantages over the varchar type that’s normally used to store email addresses. One advantage is that this UDT provides data validation that prevents invalid email addresses from being stored in the […]
Read More...SQL that works with a user-defined type
Code examples below show some SQL statements that work with the Email type defined in previous section “A user-defined type for an email address“. In general, these SQL statements show that you can use the Email type just like you use the built-in SQL Server types. However, there are a few differences. For example, if […]
Read More...