This page has 0 threads | Add post

Scalar User-Defined Functions in SQL Server

Written by Andrew Gould

In this tutorial

Built-In SQL Server Functions
I Can't Find the Function I Need!
An Important Note on Performance

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 tutorial 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:

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:

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!

Previous
tutorial

Stored procedures

Previous
part

Top of page

Next
part

Writing Custom Functions in SQL Server

Next
tutorial

Entering and changing data

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

User-defined functions

Scalar User-Defined Functions in SQL Server

Built-In SQL Server Functions

I Can't Find the Function I Need!

An Important Note on Performance

Head office

London

Manchester