User-Defined Functions

While user defined functions can offer great convenience, they can also sometimes hurt performance. This problem with them is that they use row-by-row processing, similar to how a cursor works, instead of working as a set-based operation. So if the result set of your query, which is using a user defined function, is very small, then the performance hit will be small. But if the result set is large, then performance could very well become a problem. Generally speaking, if you are using a user defined function, you will want to avoid using them with large result sets. Instead, use a well-written stored procedure instead. [2000, 2005] Updated 9-18-2006

*****

Most of you are probably familiar with the aggregate SUM() function and how it works. Occasionally, it would be nice if SQL Server had a PRODUCT() function, which it does not. While SUM() is used to sum a group a data, the theoretical PRODUCT() function would find the product of a group of data.

One way around the problem of there not being a PRODUCT() function in SQL Server is to use some combination of a cursor and/or temporary tables. As you can imagine, this would not be very efficient. A better choice would be to use a set-based function, like the theoretical PRODUCT() function.

With a little algebra, you can simulate a PRODUCT() function in SQL Server using the built-in SQL Server LOG10(), POWER(), and SUM() function working together. This is because logarithms allow you to find the product of numbers by summing them. This was how the products of large numbers were found before the days of calculators. (Are you old enough to remember using logarithm tables in school? I am. Ouch!)

Below is a very simple example of how you can use a combination of the LOG10(), POWER(), and SUM() functions in SQL Server to simulate a PRODUCT() function. You will probably want to modify it to meet your specific needs, such as to eliminate null data, zero data, or data that might be negative.

SELECT column_name1, POWER(10,SUM(LOG10(column_name2))) AS Product
FROM table_name
GROUP BY column_name1

For example, let's look at the following to see how this works.

Record 1 (1000, 2)
Record 2 (1000, 2)
Record 3 (1000, 2)
Record 4 (1001, 3)
Record 5 (1001, 3)

Our goal here is find the product of all the records where column_name1 = 1000 and to find the product of all the records where column_name_name1 = 1001. When the above query is run, we get these results:

1000, 8
1001, 9

What has happened is that where column_name1 = 1000 (which are the first three records in our sample data), the values in column_name2 (which are 2 and 2 and 2) are multiplied together to return 8. In addition, where column_name1 = 1001 (which are the last two records in our sample data), the values in column_name2 (which are 3 and 3) are multiplied together to return 9.

Creating your own PRODUCT() function produces much faster results than trying to accomplish the same task by using a cursor and/or temporary table. [6.5, 7.0, 2000, 2005] Updated 9-18-2006