user-defined
functions in SQL Server
In the simplest
terms, a user-defined function (UDF) in SQL Server is a programming construct
that accepts parameters, does work that typically makes use of the accepted
parameters, and returns a type of result.
Types of
UDFs
Table-valued
functions
A table-valued UDF
is a function that accepts parameters and returns the results in the form of a
table. This type of function is special because it returns a table that you can
query the results of and join with other tables.
Scalar-valued
functions
A scalar-valued
UDF accepts parameters and, ultimately, returns a single, atomic value.
There are two
types of scalar-valued UDFs: deterministic and non-deterministic. Recognising
the determinism of the functions that are created is important. An example of
the importance is the creation of indexed views. One of the many restrictions
of creating an index on a view is that the view definition cannot use a
non-deterministic function.
Deterministic
A deterministic
UDF always returns the same result with the same set of input parameters. Some
examples of deterministic functions are the system functions MONTH(), YEAR(),
and ISNULL().
Non-deterministic
A
non-deterministic UDF is can potentially return a different value each time it
is called with the same set of input parameters. Some examples of
non-deterministic functions are the system functions GETDATE(), NEWID(), and
@@CONNECTIONS
.
Two examples of
UDFs
Before presenting
the examples, I will set up my SalesHistory table and load data into it:
IF
OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
CREATE TABLE
[dbo].[SalesHistory]
(
[SaleID] [int]
IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product]
[varchar](10) NULL,
[SaleDate]
[datetime] NULL,
[SalePrice] [money]
NULL
)
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i
<=1000)
BEGIN
INSERT INTO
SalesHistory(Product, SaleDate, SalePrice)
VALUES ('Computer',
DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO
SalesHistory(Product, SaleDate, SalePrice)
VALUES('BigScreen',
DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO
SalesHistory(Product, SaleDate, SalePrice)
VALUES('PoolTable',
DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
GO
The first UDF I
will look at is the scalar-valued UDF. The script below defines a function
named dbo.udf_GetProductSales that accepts three parameters and returns a MONEY
value. The function uses the three input parameters as criteria in calculating
the total sales from the SalesHistory table.
CREATE FUNCTION
dbo.udf_GetProductSales
(
@Product
VARCHAR(10),
@BeginDate
DATETIME,
@EndDate DATETIME
)
RETURNS MONEY
AS
BEGIN
DECLARE @Sales
MONEY
SELECT @Sales =
SUM(SalePrice)
FROM SalesHistory
WHERE
Product = @Product
AND
SaleDate BETWEEN
@BeginDate AND @EndDate
RETURN(@Sales)
END
The script below
calls the UDF created in the above script. Note: The schema the function
belongs to must be used in the call. In this case, the function belongs to the
dbo schema.
SELECT
dbo.udf_GetProductSales('PoolTable', '1/1/1990', '1/1/2000')
I usually
discourage using scalar-valued UDFs in a WHERE criteria statement because, for
every record considered in the query, the scalar-valued function will be called.
This means that a function used in the WHERE criteria will cause a scan of the
values being searched, which is going to be slower than if an index is able to
be used. (I will provide more details on this concept in a future article.)
Although the use
of a correlated sub-query is sometimes confusing and complicated, the use of
them can help solve some of the more challenging query problems. While using
these special queries is useful, they only return one column of data. You can
use the upgraded table-valued UDFs in SQL Server 2005 to overcome this
shortcoming. I'll show you how to use the APPLY operator to accept column
values from a table and return a table-result of correlated values.
CREATE FUNCTION
dbo.udf_GetProductSalesTable
(
@Product VARCHAR(10),
@SaleID INT
)
RETURNS @SalesTable
TABLE
(
SalesTotal MONEY,
SalesCount INT
)
BEGIN
INSERT INTO
@SalesTable(SalesTotal, SalesCount)
SELECT
SUM(SalePrice),
COUNT(SaleID)
FROM
SalesHistory
WHERE
Product = @Product
AND
SaleID <=
@SaleID
RETURN
END
GO
The above function
accepts the particular product for which we were searching, along with the
SaleID from the SalesHistory table. From the function definition, you can see
that the function returns a table named @SalesTable that contains two columns:
SalesTotal and SalesCount. The body of the function inserts aggregate values
into the @SalesTable table variable based upon the input parameters.
The following code
uses the APPLY operator to invoke the table-valued function with the values
from the SalesHistory table. (Note: Logically, you may want to use a JOIN
operator here, but it is not necessary. The APPLY operator essentially does the
"JOIN" for us by applying the values from the SalesHistory table to
the table-valued function. In a sense, this code works the same way a
correlated sub-query does, except that it can return multiple correlated
values.)
SELECT * FROM
SalesHistory sh
CROSS APPLY
dbo.udf_GetProductSalesTable(sh.Product, sh.SaleID)
ORDER BY sh.SaleID
ASC
select * from dbo.udf_GetProductSalesTable('Computer',
60)
No comments:
Post a Comment