SQL Server Series — Table Valued Functions

Have you ever wanted to have a “parameterized” view in SQL Server? If so, this article is for you.

SQL Server table valued functions are a powerful way to encapsulate logic that can be re-used through procedures and queries to save time and processing power.

We are going to walk through a simplistic example to demonstrate how in-line table valued functions are created, and how they can be used inside of a query.

Step 1 — Create and populate testing tables

Start by creating and populating the two tables below. (Note that the employees table has one additional employee “John” that the clock in table does not contain. This is done purposefully, and will be explained later in the article).

CREATE TABLE tblEmployees
(
EmployeeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
EmployeeName varchar (100)
)

INSERT INTO tblEmployees (EmployeeName)
VALUES 
('Jack'),
('Jill'),
('Jim'),
('Jane'),
('John');

CREATE TABLE tblEmployeeClockInTime
(
EmployeeName varchar (100),
ClockInTime datetime
)

INSERT INTO tblEmployeeClockInTime (EmployeeName, ClockInTime)
VALUES 
('Jack', getdate()),
('Jill', getdate()),
('Jim', getdate()),
('Jane', getdate());

WAITFOR DELAY '00:00:10'

INSERT INTO tblEmployeeClockInTime (EmployeeName, ClockInTime)
VALUES 
('Jack', getdate()),
('Jill', getdate()),
('Jim', getdate()),
('Jane', getdate());

The “WAITFOR DELAY ‘00:00:10” is used so that we 10 seconds between the time when the first batch of records are inserted, and when the second batch of records are inserted. So, each employee will have two records inside of the clock in table, with dates and times that are ten seconds apart.

Step 2 — Create the in-line table valued function

Now, proceed to create the following function. Note that this function is considered to be an “in-line” table valued function as it does not have a BEGIN/END block, and is returning a table directly, as opposed to a table variable. In many instances, in-line table valued functions can have significant performance improvement over “multi statement” table valued functions.

CREATE FUNCTION fnTblEmployeeMostRecentClockIn (@EmployeeName varchar(100))
RETURNS TABLE
AS
RETURN
SELECT MostRecentClockIn
FROM (
  SELECT
   ClockInTime AS MostRecentClockIn,
   ROW_NUMBER() OVER (PARTITION BY EmployeeName ORDER BY ClockInTime DESC) AS RN
  FROM tblEmployeeClockInTime
  WHERE EmployeeName = @EmployeeName
 ) EMPTIME
WHERE EMPTIME.RN = 1

This function is very simple. It takes one input parameter (employee name), and provides an output with the maximum, or most recent, clock in date and time of the employee.

Step 3 — Using the table valued function as a stand alone query

Get Perry Lewis’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Run the query below to output the most recent clock in time for employee “Jane”.

SELECT *
FROM fnTblEmployeeMostRecentClockIn('Jane')

You will notice the result has one column named “MostRecentClockIn” with a date and time stamp.

To validate that our function returned the most recent clock in time, the query below can provide the records associated with this employee, starting with the most recent.

SELECT *
FROM tblEmployeeClockInTime
WHERE EmployeeName = 'Jane'
ORDER BY ClockInTime DESC

We can see that the function selected the record associated with the most recent clock in, as the seconds are listed as “33”, where the prior record had the seconds listed as “23”.

Step 4 — Using the table valued function as a joined table in a query

Table valued functions support two special operators in SQL Server:

CROSS APPLY: Synonymous with an INNER join. Result set will only return rows that are matched by both tables.

OUTER APPLY: Synonymous with a LEFT join. Result set will return all rows from the initial table, as well as matching results that are retrieved from the table valued function.

To run the new function against our employee table, the SQL below can be used.

/* USED FOR "LEFT" JOIN*/
SELECT 
 E.*,
 ClockTime.*
FROM tblEmployees E
 OUTER APPLY fnTblEmployeeMostRecentClockIn(E.EmployeeName) ClockTime

/* USED FOR "INNER" JOIN*/
SELECT 
 E.*,
 ClockTime.*
FROM tblEmployees E
 CROSS APPLY fnTblEmployeeMostRecentClockIn(E.EmployeeName) ClockTime

Note that the SQL above is passing in the employee name from the tblEmployees table. This allows the function to operate on all rows of the source table, and return the respective values for employee.

When we run the “OUTER APPLY” version, we get the most recent clock in time for all employees, except John. This is because John does not have any records inside the tblEmployeeClockInTime table.

When we run the “CROSS APPLY” version, we get the most recent clock in time for all employees that have an existing record in both the source table table, as well as the table valued function. Notice that “John” is excluded from the result set, as he does not have any records in this table.

Voila! You have created and implemented an in-line table valued function that can be re-used throughout queries and procedures as needed, without the need to constantly re-write logic, and ensure that performance is top of mind.