¿Por qué debemos evitar funciones escalares T-SQL?

Hoy me he decidido a escribir sobre por qué las funciones escalares T-SQL no son buena opción. Esto es otra vez mas, algo que veo como un patrón recurrente en todos los clientes que visito y siempre acabo recomendando que sean eliminados, aunque lamentáblemente muchas veces solo se hace en los escenarios críticos…es decir, la/las consultas o patrones T-SQL maléficos que les detecto que están destrozando a su pobrecito SQL Server

Antes de nada me gustaria recordar las razones por las que evitar siempre utilizar una funcion escalar en SQL Server:

1. No son visibles en los planes de ejecución
2. Producen malísimas estimaciones estadísticas que derivan en el uso inadecuado de operadores NESTED LOOPS
3. El código de la función se interpreta en cada llamada (cada fila)
4. Por último y lo más importante de todo: NO ES POSIBLE EL PARALELISMO

Independientemente de cual sea la razón por las que uno piense en utilizarlas, hay que quitarse la idea de la cabeza. Y no, no me vale eso de “pero esque así la consulta T-SQL queda más limpia y bonita” o “esque así reutilizo código”. 

Para rebatir cualquier afirmación pienso que un ejemplo vale más que 1000 palabras:

use tempdb
go

if (object_id('dbo.TablaGrande') is not null)
    drop table dbo.TablaGrande
go

-- Creamos una tablita con 100k filas para el ejemplo
--
with
    t0 as (select n = 1 union all select n = 1),
    t1 as (select n = 1 from t0 as a, t0 as b),
    t2 as (select n = 1 from t1 as a, t1 as b),
    t3 as (select n = 1 from t2 as a, t2 as b),
    t4 as (select n = 1 from t3 as a, t3 as b),
    t5 as (select n = 1 from t4 as a, t4 as b),
    result as (select row_number() over (order by n) as n from t5)    
select n col1 ,n+1 col2
into dbo.TablaGrande
from result where n <= 1000000

-- Le creo 2 índices, por hacer algo, realmente tampoco es 
-- imprescindible
create clustered index ci_tablagrande on TablaGrande(col1 asc)
create nonclustered index nci2_tablaGrande on TablaGrande(col2 asc)

-- Creamos una función tonta para ilustrar que incluso la cosa
-- mas increiblemente tonta produce resultados desastrosos
--
if (object_id('dbo.ChorraFuncion') is not null)
    drop function dbo.ChorraFuncion
go
create function dbo.ChorraFuncion( @a int,@b int)
returns varchar(100)
begin
return cast((@a+@b) as varchar(100))
end

-- ponemos en marcha la salida de tiempos
--
set statistics time on

-- Para no hacer trampas, borramos cualquier página útil 
-- para que sean ejecuciones vírgenes
dbcc dropcleanbuffers
go

-- Usando la función, aqui vemos los tiempos en mi máquina:
-- 
-- SQL Server Execution Times:
--  CPU time = 5444 ms,  elapsed time = 6229 ms.
--
select max(dbo.ChorraFuncion(t1.col1,t2.col2))
from TablaGrande t1
    inner join TablaGrande t2 on t1.col1 = t2.col2
go

dbcc dropcleanbuffers

 
-- Expandiendo la función (sin llamarla, pero haciendo lo mismo que hace), vemos como los tiempos son radicalmente mejores
-- SQL Server Execution Times:
--  CPU time = 3711 ms,  elapsed time = 1118 ms.
--
select max(cast((t1.col1 + t2.col2) as varchar(1000)))
from TablaGrande t1
    inner join TablaGrande t2 on t1.col1 = t2.col2
go

La razón de esto ya la he comentado anteriormente, pero quizás se aprecie mejor viendo por un lado el plan de ejecución:

En serie cuando lanzamos la consulta con la llamada a ChorraFunción:

Y en paralelo cuando no la usamos:

Por cierto…¿Ves por algún lado en el plan de ejecución en serie referencia alguna a la función? (si, es eso que pone “Compute Scalar” y que afirma erroneamente que tiene coste 0%.

Además, si dejamos puesto el SQL Server profiler, en la ejecución de la query que usa ChorraFuncion podemos ver si capturamos el evento SP:SmptCompleted, las ejecuciones de la función que hay por cada una de las filas procesadas (en este caso han sido 1 millon de ejecuiones)