User Defined Functions

BrandonGalderisi — Sat, 07 Mar 2009 17:50:00 GMT

User defined functions have many uses in SQL Server. Functions are useful to help clean up complex string manipulation logic in other areas of code and many other things. But convenience is not free. There is overhead in using functions and we will go over that later in the article.

There are two types of User Defined Functions in SQL Server, Scalar Valued and Table Valued Functions. Scalar Valued functions are used to return a single value whereas Table Valued functions return a table. The table can be one or more columns and one or more rows. While the structure for creating each type of function is nearly identical, how you use them is different.

First let's create a basic Scalar Valued function to show the proper way to construct your function. For Scalar Valued Functions, you start with the keywords "create function". The next thing is the name of the function. While it is not necessary to provide the schema name when naming the function, you will need to provide the schema name when using the function. The next thing you provide is the keyword "returns" followed by the data type to be returned. Returns says "This is the type of value that the function will return. The next thing is the keyword "as". Like in stored procedures, it separates the definition of the function from the code of the function. The next thing to know is that the code in a function MUST be contained within a BEGIN/END block. And the last thing to know is that the last thing that a function must do is RETURN the value.

This basic function uses the dateadd and datediff functions to strip the time portion of the @DateTime parameter to have it return the DATE at midnight.

create function dbo.fn_GetDateOnly(@DateTime datetime)
returns datetime
as
begin
return dateadd(d, datediff(d, 0, @DateTime), 0)
end
go

Now how do we use scalar valued functions? You use the function by selecting it.

select dbo.fn_GetDateOnly(getdate())

The result will be the the date which you run it with the time of midnight.

You can also use it to format a datetime column as you select it from your table.

select dbo.fn_GetDateOnly(SomeDateField) as SomeDate
from SomeTable

Next we will create a simple Table Valued Function. This function accepts a datetime parameter and then inserts the date of, before and after into the table for the function.

create function dbo.fn_DatesTable(@BaseDate datetime)
returns @Dates table (theDate datetime)
as
begin
insert into @Dates (Thedate)
values(Dateadd(d, datediff(d, 0, @BaseDate)-1, 0))
insert into @Dates (Thedate)
values(Dateadd(d, datediff(d, 0, @BaseDate), 0))
insert into @Dates (Thedate)
values(Dateadd(d, datediff(d, 0, @BaseDate)+1, 0))
return
end
GO

The below SQL shows you the difference in calling a Table Valued function as opposed to Scalar Valued functions. You don't select it, you select from it.

select * from dbo.fn_datestable('2009-03-07 12:00 pm')

The results will be:

Another, less commonly used syntax for creating a table valued function is below. This syntax is useful when you are using a user defined function in place of a view because you need to specify parameters.

create function dbo.fn_dosomething()
returns  TABLE
as

return 
(         select cast(1 as int) as Id, cast(N'Hello' as nvarchar(50)) as theValue
union all select 2,'Good Bye'
)
GO

Now comes the bad news. While functions are useful tools, they are not for every scenario. There is some overhead to using functions. The below Test script will loop 100,000 times and assign the function

declare 
      @SampleDate   datetime
     ,@dt           datetime
     ,@dt2          datetime
     ,@i            int
declare @res table
     (Method        varchar(20)
     ,Time_In_Ms    int
     )
set @SampleDate = getdate()
set @dt = getdate()
set @i=1
while @i<100000
begin
     set @dt2=dbo.fn_GetDateOnly(@sampleDate+@i)
set @i=@i+1
end
insert into @Res(Method,time_In_Ms)
values('Function', datediff(ms, @dt, getdate()))

set @dt = getdate()
set @i=1
while @i<100000
begin
     set @dt2=dateadd(d, datediff(d, 0, @SampleDate+@i), 0)
set @i=@i+1
end
insert into @Res(Method,time_In_Ms)
values('SQL', datediff(ms, @dt, getdate()))
select * from @Res

As you can see by this output, executing the same statement through a function is slower than using an inline SQL.

Now that you know the basics of functions, you are ready to get into some tougher examples. Look for more postings specific on scalar and table valued functions.

How Tos : scalar valued function, table valued function

User Defined Functions