Came across an interesting SQL problem where a customer has a table with a primary key and X numerical fields. For each record the maximum value in these X fields was needed.
The issue was how to accomplish this efficiently? Or more accurately the least inefficient way of doing it is.
One option suggested was to have to UNION the table with itself X times and then do a MAX aggregation since there was a lot of data.
The more interesting option, which it turns out not many people are aware of, is to use a Scalar User Defined Function (UDF) to calculate the maximum value.
Here's an example of this in action:
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)
Basically the scalar UDF is called for each row returned from a SELECT. The column itself is not actually stored in the SQL Server table. Any INSERT and UPDATE on the table do not calculate the value for the computed column. Now obviously this is an overhead that may not be acceptable if the table is very static and read a lot. Here, it may be more efficient to perform the calculation once on INSERT or UPDATE of the table with INSTEAD OF triggers or another similar method.