Implement Programming Objects: Create, use, and alter user-defined functions (UDFs)

(This is part of the Study Guide series, 70-464)

Microsoft’s Measured Skill description: This objective may include but is not limited to: understand deterministic and non-deterministic functions; use cross apply with UDFs; Common Language Runtime (CLR)

What I see:

·         deterministic vs. non-deterministic

·         CROSS APPLY with UDFs

·         SQLCLR functions

Deterministic VS. Non-Deterministic

                I am going to heavily reference BOL about a good description of deterministic and non-deterministic functions.  In short, a deterministic function will always return the same value with the given input, and a non-deterministic function doesn’t have the requirement (i.e. GETDATE()).

CROSS APPLY with UDFs

                You can CROSS APPLY a table-valued function in order to get correlated values.  The below example shows how this can be done:

create table dbo.UdfCrossApplyTest

(

       id int identity(1, 1) not null,

       SomeString nvarchar(128) not null

              default replicate('a', 128)

);

go

insert into dbo.UdfCrossApplyTest

values(default);

go 10

create function dbo.AddOne

(

       @original_number int

)

returns table

as

       return

       (

              select

                     @original_number as original_number,

                     @original_number + 1 as new_number

       )

go

select

       tbl.*,

       udf.new_number

from dbo.UdfCrossApplyTest tbl

cross apply dbo.AddOne(tbl.id) udf

SQLCLR Functions

                Just like stored procedures (as seen in a recent blog post), SQLCLR can also be used to created functions.  I will show a working example of how this can be accomplished, but the process is almost identical to creating SQLCLR stored procs.

Write the below code in Visual Studio (or a text editor, but you will then need to externally implement the C# compiler) and build into an assembly:

using System;

using Microsoft.SqlServer.Server;

public class IntFunc

{

    [SqlFunction]

    public static int AddOne(int original_number)

    {

        return original_number + 1;

    }

}

Then once the above is compiled into a class library, you can go to the SQL Server side to create our SQLCLR function, and run a quick test to just show us that it works:

use MeasureMySkills;

go

create assembly IntFunctions

from 'C:\SqlServer\SQLCLR\IntFunctions\IntFunctions\bin\Release\IntFunctions.dll'

with permission_set = safe;

go

create function ClrAddOne

(

       @original_number int

)

returns int

as external name IntFunctions.IntFunc.AddOne;

create table IdentityTbl

(

       id int identity(1, 1) not null,

       SomeString nvarchar(128) not null

              default replicate('a', 128)

);

go

insert into IdentityTbl

values(default);

go 10

select

       id,

       SomeString,

       dbo.ClrAddOne(id)

from IdentityTbl;

References

·         BOL reference on deterministic vs. non-deterministic functions

·         BOL reference on CREATE FUNCTION

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.