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

Bookmark the permalink.

Comments are closed.