USER-DEFINED AGGREGATES Just because an application uses objects

USER-DEFINED AGGREGATES Just because an application uses objects to represent things that are not scalars does not mean that the database has to. Chapter 8 covers the XML data type, and Chapter 14 covers ObjectSpaces. SQL Server 2005 also still supports SQLXML. All these technologies are much more efficient for persisting objects in a relational database than user-defined types are, and best practice is to use these technologies for persisting nonscalar objects into SQL Server. User-Defined Aggregates An aggregate function operates on a set of rows and produces a scalar. The SUM function in SQL Server is an example of an aggregate. A SELECT statement is used to produce a set of rows, and the SUM function produces the arithmetic sum of some column in the rows produced by the SELECT statement. Listing 5-25 shows an example of a SQL batch using the SUMfunction. Listing 5-25: Using the SUM Aggregate Function CREATE TABLE Items ( size int, price float ) GO INSERT INTO Items VALUES (3, 12.0) INSERT INTO Items VALUES (5, 10.0) INSERT INTO Items VALUES (3, 1.0) INSERT INTO Items VALUES (3, 2.0) GO SELECT SUM(price) FROM ITEMS WHERE size = 3 GO The SELECT statement made a set of all the rows from the Items table where the size=3, and then the SUM function added up each of the price columns from those rows to produce 15. This ability to do operations that produce a scalar result on sets of data is a key feature of a relational database. In fact, the SQL-92 specification requires that a database implement the COUNT, SUM, AVG, MAX, and MIN aggregate functions for compliance. SQL Server includes these aggregate functions and a number of others for example, COUNT and COUNT_BIG, plus a number of statistical aggregates such as STDEVand VARfor standard deviation and variance.

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 5/2/94 6/2/94 7/2/94 8/2/94

USER-DEFINED TYPES AND AGGREGATES 5/2/94 6/2/94 7/2/94 8/2/94 d1 d2 d3 d4 Time abs(d2 – d4) = 2 months abs(d2 – d4) = 2 months abs(d1 – d2) = 1 month abs(d3 – d2) = 1 month abs(d1 – d4) = 3 months abs(d3 – d4) = 1 month So d1 is not between d2 and d4 So d3 is between d2 and d4 Figure 5-1: Answering the Between Question for Dates Because the distance between p1 and p2 and between p2 and p3 is less than the distance between p1 and p3, p2 is between p1 and p3. Also, the distance between p4 and p1 and between p4 and p2 is less than the distance between p1 and p2, so p4 is between p1 and p2. But it is obvious that the distance between p4 and p3 is greater than the distance between p1 and p3, so p4 could not be considered to be between p1 and p3, even though it is between p1 and p2. So it is not behaving like a scalar. If you could find a set of rules for answering the between question consistently for points, you could consider a point to be a scalar. However, no such set of rules exists, so a point is not a scalar, even though it has fewer fields than a date does. User-defined types should be used only for objects that represent scalars, even though it is physically possible to use user-defined types to represent any object whose state is less than 8,000 bytes, just by implementing IBinarySerialize. p1 p2 p3 dist = 1.1 dist = 1dist = 1 p4 dist = 0.9 dist = 0.9 p2 is between p1 and p3 p4 is between p1 and p2 So p4 is between p1 and p3?? Figure 5-2: Answering the Between Question for Points

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