Today I am writing to you as I promised to see the solutions to the performance problems involved in using the scalar functions in more complex queries.

The first is to create the scalar function as a table function, that is, instead of returning a single value, a set of values ​​as a normal query.

We create the function:

CREATE FUNCTION fn_ NombreUsuarioTableFunction (@IdUsuario int)
RETURNS TABLE 
AS RETURN 
(SELECT  U.LastName + ', ' +  U.FirstName [NombreCompleto], D.Departamento  
FROM Usuarios 
 JOIN DEPARTAMENTOS D ON U.IdDepartamento = D.IDDepartamento
WHERE IdUsuario = @IdUsuario)
GO

The scalar function scalar function was used as follows:

SELECT NombreUsuario(U.IDUsuario) AS NombreCompleto, U.Departamento 
 FROM USUARIOS 
JOIN Ventas V ON U.Idusuario = V.IdUsuario 
WHERE NombreUsuario(U.IDUsuario) = 'Javier Pérez Pérez'

To use the table function we have to use a CROSS JOIN as follows:

SELECT U.NombreCompleto, U.Departamento 
FROM Ventas V
 CROSS APPLY NombreUsuarioTableFunction (V.IDUsuario) U 
WHERE U.NombreCompleto = 'Javier Pérez Pérez'

In this way the number of I/O is much more efficient and CPU usage is much lower.

The other alternative is to create a view. For example:

CREATE VIEW VistaNombreUsuario 
AS 
SELECT U.IdUsuario, U.LastName + ', ' +  U.FirstName [NombreCompleto], D.Departamento 
FROM Usuarios 
 JOIN DEPARTAMENTOS D ON U.IdDepartamento = D.IDDepartamento
GO

Now we make a JOIN with the view:

SELECT U.NombreCompleto, U.Departamento 
FROM Ventas V 
  JOIN VistaNombreUsuario U ON U.IdUsuario = V.IdUsuario 
U.NombreCompleto = 'Javier Pérez Pérez'

With the view we gain that the CPU usage is a bit lower although the I / O is the same as with the use of CROSS JOIN.

If we put together that it is a little better to use the CPU using a view, this is my preferred way, because it is more understandable for me than using the CROSS JOIN. In addition, the view is always more generic to be able to filter what we want or without any filter, what has the functions that we have to pass the filter beforehand and what allows us to create other functions so that it does not filter anything or to pass more filters or other different.

I have not put any performance test because here I leave an article which explains very well the yields attaching tests with the profiler. It has no waste and is essential if you are interested in the performance tests of everything I have told you.