SQL Server: User Defined Functions

In SQL Server 2008, user-defined functions may be one of three types: scalar-valued, inline table-valued, or multi-statement table-valued. Both inline table-valued and multi-statement table-valued functions can be used to return a result set as a table data type. Inline table-valued functions can execute only one SQL statement. Multi-statement table-valued functions can execute multiple SQL statements as well as include other procedural code. An inline table-valued function can be an alternative to creating a view, and provides the benefit of being able to specify parameters within the SELECT statement. Inline views are sometimes referred to as parameterized views. In this scenario, because the user-defined function will not include multiple SQL statements and only needs to return rows based on the parameters it was passed, DBA/Developer should use an inline table-valued function. To create an inline table-valued function, use the CREATE FUNCTION statement and specify a table data type as the return value. Unlike a multi-statement table-valued function, an inline table-valued function does not have to define the structure of the table being returned. The structure of the returned table is derived by the SELECT statement included within the function. For example, the following statement would create an inline table-valued function that accepts two parameters and returns a table of data based on a query of two tables:

CREATE FUNCTION dbo.GetData

(@typedesc varchar(10), @loc int)
RETURNS table AS RETURN
(SELECT i.ProductName, t.Description
FROM InventoryHistory
AS iJOIN PTLookup AS t
ON t.TypeID = i.TypeID
WHERE t.Description = @typedesc
AND i.LocID =@loc);

This would create an inline table-valued function named dbo.GetData that users could use to query the InventoryHistory table and the PTLookup lookup table. Users would be able to call the function from within a FROM clause, passing it the search parameters to return the data and perform additional analysis. DBA/Developer should not create a recursive scalar user-defined function. A scalar user-defined function returns a single scalar value, and in this scenario, DBA/Developer wants users to access a result set. DBA/Developer should not create a multi-statement table-valued function because this would require more development effort. With a multi-statement table-valued function, DBA/Developer must explicitly define the table being returned, but with an inline table-valued function, DBA/Developer do not.DBA/Developer should not create a partitioned view on the InventoryHistory table. A partitioned view is used when DBA/Developer have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented using the UNION ALL operator. For example, if DBA/Developer had three separate tables with an identical structure, DBA/Developer might use the following statement to create a partitioned view that allows users to query data from all three tables:

CREATE VIEW PartView

AS SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3;