Design T-SQL table-valued and scalar functions.
This objective may include but is not limited to: inline table-valued functions vs. views, multi-statement table-valued functions, determinism
There are many types of user defined functions available, including functions which return a scalar (single value), those which return a table (multiple rows), those that perform a complex calculation and those that primarily access SQL Server Data. From the title of the section we would only be concerned with Table-Valued (Inline or Multi-Statement) and Scalar functions. Looking at the list of skills, I’m not sure where the scalar functions comes into play here. Perhaps in the discussion with determinism? In any case, it appears we are most concerned with designing user-defined functions. The examples provided are to show of the syntax only and are not meant to be useful or efficient!
- Scalar Functions, Inlining, and performance: An entertaining title for a boring post (Adam Machanic)
- Table Valued Functions (Grant Fritchey)
Inline Table-Valued Functions vs. Views
An inline table-valued function is a function which returns a table data type and are often used to perform as a parameterized view. The RETURNS clause must contain only the keyword of TABLE since Inline functions do not need to have a format for the returned variable defined, as this will be the same as the result set of the SELECT statement in the function. There is no BEGIN and END block, only the RETURN clause which contains only the single SELECT statement in parentheses. The select statements of inline table-valued functions are subject to the same restrictions as views. Finally, these functions only accept constants or local variable arguments.
With the ability to accept parameters, and the freedom to be used anyplace where a table or view is used, it can be seen that in-line table valued functions can be a very powerful alternative to views. Like Views, In-Line Table-Valued Functions are expanded by the optimizer and will use the relevant statistics. Unlike views, they force the user to provide the necessary parameters to limit data. Another thing to remember is that, unlike views, Inline Table-Valued Functions cannot be used to update data.
In-Line Table-Valued Functions further empower the use of indexed views by allowing the indexed view to be created and maintained but accessed in such a way that the parameters which will narrow the results are required for efficiency
Example Inline Table-Valued Function
IF OBJECT_ID (N’[dbo].[AnExample]‘, N’IF’) IS NOT NULL
DROP FUNCTION [dbo].[AnExample];
CREATE FUNCTION [dbo].[AnExample](@myText varchar(25))
TextColumn = @myText
Multi-Statement Table-Valued Functions
Multi-Statement Table-Valued Functions, like inline-table valued functions, return a table data type and can be used as a parameterized view. However, unlike inline-table valued functions, multi-statement functions require a BEGIN and END block as well as the definition of the table variable to be returned. In the block the table variable will need to be explicitly populated.
It should be noted that there are dangers to the use of Multi-Statement Table-Valued functions. Since they do not capture statistics, the optimization engine will act as if they were going to return a single record.
Example Multi-Statement Table-Valued Function
IF OBJECT_ID (N’[dbo].[NextYearsWorthOfSaturdaysNotFirst]‘, N’TF’) IS NOT NULL
DROP FUNCTION [dbo].[NextYearsWorthOfSaturdaysNotFirst];
CREATE FUNCTION [dbo].[NextYearsWorthOfSaturdaysNotFirst](@INPUT_DATE datetime)
RETURNS @myReturnTable TABLE
myDate datetime NOT NULL
DECLARE @NumberOfDays as int = DateDiff(dd, GetDate(), DateAdd(year, 1, GetDate()));
Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
Nbrs_Final ( n ) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs )
DateAdd(dd, n, GetDate())
n <= @NumberOfDays
Delete From @myReturnTable Where DatePart(dw, myDate) <> 6 OR DATEPART(day, myDate) = 1
DECLARE @myDate datetime = GetDate();
Determinism refers to the state of a function being either a deterministic or non-deterministic function. Deterministic functions will always return the same result given the same input and underlying data (ex COALESCE(), MONTH()). Non-deterministic functions may return different input each time that they are called with a specific input, even if the underlying data is the same (ex GETDATE() or NEWID()).
Another characteristic that functions have is the IsPrecise, which states whether or not a function is precise. In other words, does the function contain floating point operations.
Example Scalar Function
IF OBJECT_ID (N’[dbo].[UnixEpoch]‘, N’FN’) IS NOT NULL
DROP FUNCTION [dbo].[UnixEpoch];
CREATE FUNCTION [dbo].[UnixEpoch] (@INPUT_DATE datetime)
WITH EXECUTE AS CALLER
RETURN(DATEDIFF(ss, ’1970-01-01′, @INPUT_DATE));
SELECT [Workspace].[dbo].[UnixEpoch] (
GETDATE()) as myUnixEpoch;