Introduction into SQL Server Functions, their benefits and drawbacks
What is an SQL Function?
SQL Server functions can be used to return either single(scaler) values, or tables, using T-SQL or CLR (common language run-time) routines and often performing more complex calculations than you would want to use in general code.
When is it a good idea to use a function rather than inline code?
Good Use
Functions can be used to replace views (return a table), as a calculated column in a table, perform consistent lookup actions or simply just to modularise your code which can help in reducing changes needed.
Bad Use
We see it all the time, but functions should not be used to return lookup data in place of a join when you are dealing with large datasets. Each row will call the same function even if it has already encountered that value. In these cases, use a join.
Scaler function examples
Scaler functions are best used to perform logic like row based re-formatting or calculations as by their nature they are called for every row, they can be used for looking up data in another table, but in general, you will get better performance by using a join. For this, we can look at our get age function on the following link.
Storing someone’s age at the time they filled out a form would make no sense, as when the data is queried later it will be out of date. A better option would be to capture a date of birth and calculate it on the fly. In our function we added a field until, which can be used to backdate a calculation, or perhaps more sombrely, calculate age a time of death (this function was extended for an NHS contract).
Example
CREATE FUNCTION [Dates].[GetAge](@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDScaler function examples
To use this from a fictional table we would simply use this, which would provide either current age or age at death.
Use in a select statement
SELECT [PersonID],[DateOfBirth],[dbo].[GetAge]([DateOfBirth],[DeceasedDate]) AgeAsFunction,--Simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]Scaler function examples
Advantages: Consistent, modular, more compact, potentially reduces number of changes
Disadvantages: To see the code you need to look in the function
While being generally useful, this function is extremely accurate too, because it utilises a leap year function. It is non-deterministic by nature so should never be stored as persisted data.
Table column examples
Calculated columns can be added as either persisted (changes when the data does) or non-persisted (calculated every time the row is selected). We can look at two ways we have used them here within our Content Management System.
Note: Persisted data can be harder to achieve as it requires a set of constraints to be met
Non-persisted: Age
Using the age function as above, we can add this into a table and pass in values from other columns. We then simply select it as a column.
Add to a table
CREATE TABLE [Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,[DateOfBirth] [datetime] NULL,[Age] AS ([dbo].[GetAge]([DateOfBirth],[DeceasedDate])),[DeceasedDate] [datetime] NULL)Select Statement
SELECT [PersonID],[DateOfBirth],[Age] AgeAsColumn,--Even simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]Non-persisted: Age
Advantages: Consistent, modular
Disadvantages: Slows query speed if not needed.
Persisted: Minified CSS
We have a function that reduces space needed for CSS by up to 30%. Calling this regularly would slow down the select speed of the table, and as the data is rarely updated it made sense to perform calculations at insert/update time. By creating the column as a function, we have no need to perform these operations as a trigger either.
Add to a Table
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)Persisted: Minified CSS
It can be selected just like a normal column, and data is stored in the table. It also avoids the use of a massive replace statement bloating our code.
Advantages: Consistent, modular, faster select speed, no need for a trigger!
Disadvantages: Increases space needed for table, slows insert speed
Replacing a view
We tend not to use views, apart from when we are regularly using the same joins in multiple places.
Even in these instances, there is no reason why a table function can’t be used more effectively. The table we have used can be found on the link below, and we have two examples of usage, one via a function and the other using a view.
Create a function
CREATE FUNCTION Dates.GetCalender(@DateFrom DATETIME2,@DateTo DATETIME2,@Function INT) RETURNS @D TABLE (CalenderDate DATETIME2(7) NOT NULL PRIMARY KEY,CalenderCA INT NULL,CalenderCD INT NULL,WeekDayID INT NULL,WeekDayName VARCHAR(9) NULL,HolidayType NVARCHAR(100)) AS BEGININSERT INTO @DSELECT c.*,HolidayTypeFROM [Utilities].[Dates].[Calender] cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND CalenderFunction=@FunctionWHERE c.CalenderDate BETWEEN @DateFrom AND @DateTo RETURNENDGOCreate a view
CREATE VIEW Dates.GetCalenderView ASSELECT c.CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName,h.HolidayType,c.CalenderFunctionFROM (SELECT CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName, CalenderFunction FROM [Utilities].[Dates].[Calender],(SELECT DISTINCT CalenderFunction FROM Dates.CalenderHolidays) x) cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND h.CalenderFunction=c.CalenderFunctionUsage
SELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',0) --England & WalesSELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',1) --Scotland----OR----SELECT * FROM Dates.GetCalenderView WHERE CalenderDate BETWEEN '2018-01-01' AND '2018-12-31' AND CalenderFunction=0Replacing a view
Benefits: Compact to call, returned with Primary Key (perfect for further join), parameters could be used earlier in code.
Disadvantages: More code to build, less flexible
Use in Apply Joins
Table functions are great to use in Apply Joins, as data can be passed in on a row-by-row basis. We use our TextToRows function to pull strings apart in SQL Server. In the example below we use a double apply to split the data twice with different delimiters.
SQL Code
DECLARE @TestText TABLE(TextToSplit NVARCHAR(100))INSERT INTO @TestText SELECT 'Text,To,Tows:Split,One'INSERT INTO @TestText SELECT 'Text,To,Tows:Split,Two'SELECT t.TextToSplit,s1.WordInt,s1.WordStr,s2.WordInt,s2.WordStrFROM @TestText tOUTER APPLY dbo.TextToRows(':',TextToSplit) s1OUTER APPLY dbo.TextToRows(',',WordStr) s2Further detail
Some of the functions we have written can be found below.
