SQL Server - Create Computed Column using User Defined Functions (UDFs)

User defined functions (UDFs) are small programs that you can write to perform an operation. Adding functions to the Transact SQL language has solved many code reuse issues and provided greater flexibility when programming SQL queries.

According to SQL Server Books Online, User defined functions (UDFs) in SQL Server 2000 can accept anywhere from 0 to 1024 parameters.

User defined functions (UDFs) are either scalar-valued or table-valued. Functions are scalar-valued if the RETURNS clause specified one of the scalar data types. Functions are table-valued if the RETURNS clause specified TABLE.

There are number of reasons to use user defined functions (UDFs), but this time I will share about the uses of scalar function to create a computed column. Scalar functions can be used to compute column values in table definitions. Arguments to computed column functions must be table columns, constants, or built-in functions. This example shows a table that uses a Volume function to compute the volume of a box:


-- Create function statement
CREATE FUNCTION BoxVolume
(
@BoxHeight decimal(4,1),
@BoxLength decimal(4,1),
@BoxWidth decimal(4,1)
)
RETURNS decimal(12,3)
AS
BEGIN
RETURN ( @BoxLength * @BoxWidth * @BoxHeight )
END
GO

-- Create table statement
CREATE TABLE [dbo].[BoxTable]
(
[BoxPartNmbr] [int] NOT NULL ,
[BoxColor] [varchar] (20) NULL,
[BoxHeight] [decimal](4, 1) NULL ,
[BoxLength] [decimal](4, 1) NULL ,
[BoxWidth] [decimal](4, 1) NULL ,
[BoxVol] AS ([dbo].[BoxVolume]([BoxHeight], [BoxLength], [BoxWidth]))
) ON [PRIMARY]
GO

-- Insert some data
INSERT INTO BoxTable(BoxPartNmbr,BoxColor,BoxHeight,BoxLength,BoxWidth)
VALUES (1,'RED',2,2,2)
GO
INSERT INTO BoxTable(BoxPartNmbr,BoxColor,BoxHeight,BoxLength,BoxWidth)
VALUES (2,'GREEN',3,3,3)
GO
INSERT INTO BoxTable(BoxPartNmbr,BoxColor,BoxHeight,BoxLength,BoxWidth)
VALUES (3,'BLUE',4,4,4)
GO

You must remember that a computed columns might be excluded from being indexed. An index can be created on the computed column if the user defined function is always returns the same value given the same input (deterministic).

With UDFs, you can more easily accommodate the unique requirements of a custom application. They increase functionality while often reducing your development effort.