Scalar User Defined Functions in SQL Server - UDFs
Part one of a five-part series of blogs

If you have a calculation that you frequently use in SQL queries you're probably bored of writing out the same code time after time. Why not try creating a user-defined function to save you the hassle? This blog teaches you how to define your own custom functions, including how to use input parameters, how to alter a function after you've created it, and even how to use fancy programming techniques such as variables and IF statements to help structure complex sequences of calculations.

  1. Scalar User-Defined Functions in SQL Server (this blog)
  2. Writing Custom Functions in SQL Server
  3. Creating and Using a Custom Function
  4. Altering and Deleting Custom Functions
  5. Writing Complex User-Defined Functions

This blog is part of our long and detailed online SQL tutorial.  Another way to learn SQL is on one of our SQL training courses.

Posted by Andrew Gould on 01 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Scalar User-Defined Functions in SQL Server

In SQL Server, a scalar function is one which returns a single value, be that a string of text, a number, or a date.  There are many built-in functions in SQL Server, but this blog will teach you how you can write your own user-defined functions, or UDFs.

Built-In SQL Server Functions

You may have used existing functions in your SQL queries in order to create calculated fields, such as in the example below:

SELECT

--The length of the film name

LEN(FilmName)

--Replaces NULL with custom value

,ISNULL(FilmName, 'No title entered')

--The weekday of the date

,DATENAME(DW,FilmReleaseDate)

--Date in UK format

,CONVERT(CHAR(10),FilmReleaseDate,103)

--Converts one data type into another

,CAST(FilmRunTimeMinutes AS DECIMAL)/60

FROM

tblFilm

All of the functions used in the example above perform a useful role and there are many more built-in functions available:

Built-in SQL functions

Every SQL Server database has a Programmability folder giving you access to the built-in, system functions.

 

I Can't Find the Function I Need!

Although there are many system functions available, what happens if you can't find a function that meets your needs?  In the query shown below we've used the built-in DATENAME function to create a custom format for the dates in a field:

SELECT

FilmName

,DATENAME(DW,FilmReleaseDate) + ' ' +

DATENAME(D,FilmReleaseDate) + ' ' +

DATENAME(M,FilmReleaseDate) + ' ' +

DATENAME(YY,FilmReleaseDate)

FROM

tblFilm

The four calls to the DATENAME function combine to create a single calculated column, as shown below:

Formatted dates

The result is a neatly formatted column of dates.

 

Although the expression in the query works, it's a little long and unwieldy to be typing in every time I want to format dates in that particular way.  Ideally, I'd like a function which encapsulates the logic of my expression which I can use in my queries in the same way as a built-in function.  Unfortunately, no such function exists in SQL Server.  The good news, however, is that you have the option to create your own custom functions!  Read part 2 of this blog series to discover two ways to create a user-defined function.

An Important Note on Performance

Although scalar user-defined functions can be immensely useful, they can have a dramatic negative impact on query performance.  Before you embark on a crusade to convert all of your inline expressions to custom functions it might be worth a quick Google of the phrase sql user defined function performance.  Of course, if you're anything like me, after reading all the advice you'll create a UDF anyway and test its performance for yourself!

This blog has 0 threads Add post