70-451 – MCITP – Designing Programming Objects – Design T-SQL Table-Valued and Scalar Functions

Requirements

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

My Take

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 functionsThe examples provided are to show of the syntax only and are not meant to be useful or efficient!

Other resources:

  1. Scalar Functions, Inlining, and performance: An entertaining title for a boring post (Adam Machanic)
  2. 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

USE Workspace;
GO
IF OBJECT_ID (N'[dbo].[AnExample]’, N’IF’) IS NOT NULL
    DROP FUNCTION [dbo].[AnExample];
GO
CREATE FUNCTION [dbo].[AnExample](@myText varchar(25))
RETURNS TABLE
AS
RETURN
(
    Select
     ColumnA
    From
     TestTable
    Where
     TextColumn = @myText
);
GO

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

USE Workspace;
GO
IF OBJECT_ID (N'[dbo].[NextYearsWorthOfSaturdaysNotFirst]’, N’TF’) IS NOT NULL
    DROP FUNCTION [dbo].[NextYearsWorthOfSaturdaysNotFirst];
GO
CREATE FUNCTION [dbo].[NextYearsWorthOfSaturdaysNotFirst](@INPUT_DATE datetime)
RETURNS @myReturnTable TABLE
(
myDate datetime NOT NULL
)
BEGIN
    DECLARE @NumberOfDays as int = DateDiff(dd, GetDate(), DateAdd(year, 1, GetDate()));
    WITH
     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 )
    Insert @myReturnTable
    SELECT
     DateAdd(dd, n, GetDate())
    From
     Nbrs_Final
    WHERE
     n <= @NumberOfDays
    ;
    Delete From @myReturnTable Where DatePart(dw, myDate) <> 6 OR DATEPART(day, myDate) = 1
    RETURN
END
;
GO

DECLARE @myDate datetime = GetDate();

Select
*
From
[dbo].[NextYearsWorthOfSaturdaysNotFirst] (@myDate)
;

Determinism

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

USE Workspace;
GO
IF OBJECT_ID (N'[dbo].[UnixEpoch]’, N’FN’) IS NOT NULL
    DROP FUNCTION [dbo].[UnixEpoch];
GO

CREATE FUNCTION [dbo].[UnixEpoch] (@INPUT_DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     RETURN(DATEDIFF(ss, ‘1970-01-01’, @INPUT_DATE));
END;
GO

SELECT [Workspace].[dbo].[UnixEpoch] (
   GETDATE()) as myUnixEpoch;
GO

Advertisements
This entry was posted in SQL and tagged , , . Bookmark the permalink.

2 Responses to 70-451 – MCITP – Designing Programming Objects – Design T-SQL Table-Valued and Scalar Functions

  1. Pingback: 70-451–MCITP–Designing a Database for Optimal Performance–Optimize Indexing Strategies | Destination: Change

  2. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s