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 BOLabout 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(intoriginal_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
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

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