SQL Server - Comparação de performance entre Scalar Function e CLR Scalar Function

Visualizações: 2.015 views

Neste artigo

Esse post é a parte 5 de 10 da série Performance Tuning

SQL Server – Introdução ao estudo de Performance Tuning
Entendendo o funcionamento dos índices no SQL Server
SQL Server – Como identificar uma query lenta ou “pesada” no seu banco de dados
SQL Server – Dicas de Performance Tuning: Conversão implícita? NUNCA MAIS!
SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function
SQL Server – Dicas de Performance Tuning: Qual a diferença entre Seek Predicate e Predicate?
SQL Server – Utilizando colunas calculadas (ou colunas computadas) para Performance Tuning
SQL Server – Como identificar e coletar informações de consultas demoradas utilizando Extended Events (XE)
SQL Server – Como identificar todos os índices ausentes (Missing indexes) de um banco de dados
SQL Server e Azure SQL Database: Como Identificar ocorrências de Key Lookup através da plancache

Tempo de Leitura: 13 minutos

Olá pessoal,
Tudo bem com vocês ??

Neste post, que será o número 200 do blog, eu gostaria de falar sobre dois assuntos que eu particularmente gosto muito no SQL Server, que é a criação de rotinas .NET (CLR) dentro do banco de dados SQL Server e performance tuning.

Coincidência ou não, meu post de número 100 foi o Introdução ao SQL CLR (Common Language Runtime) no SQL Server, que foi o meu primeiro post no blog sobre o CLR.

Meu intuito nesse post, é demonstrar o ganho de performance que pode ser obtido ao utilizar Scalar functions do CLR ao invés da Scalar function escrita em T-SQL (UDF – User Defined Function). Nos testes realizados, a grande maioria das funções T-SQL que foram migradas para funções CLR tiverem um grande ganho de performance simplesmente por conta das otimizações do Microsoft .NET Framework frente ao motor do SQL Server.

Em muitas situações, vemos que os índices estão criados corretamente, estatísticas atualizadas e, sem precisar reescrever a query, pode-se conseguir um grande ganho de performance simplesmente alterando a linguagem de programação de funções escalares.

Função no CLR sempre vai ser melhor?

Há quem possa ter algumas dúvidas sobre a performance de funções escalares do CLR, as quais vou esclarecer:

– As funções escalares do CLR sempre vão ter uma performance melhor que funções ?
Com certeza não! Já fiz vários testes comparando diversas funções, de diversas finalidades diferentes e pela minha experiência, as funções scalares escritas em C# no CLR, GERALMENTE apresentam sim, uma melhor performance, mas já vi alguns casos em que mesmo otimizando o código ao máximo, a função T-SQL apresenta um desempenho melhor.

– E se compararmos a performance de funções nativas com as funções do CLR ?
Funções, de uma maneira geral, sempre prejudicam a performance se uma consulta ao banco de dados. Entretanto, por mais que as funções escalares do CLR sejam bem performáticas, GERALMENTE as funções nativas do SQL Server (Ex: CONVERT, CAST, DATEADD, etc) apresentam uma melhor performance quando comparadas.

Comparando a performance na prática

Depois de fazer essa introdução sobre o assunto, é hora de realmente demonstrar o que foi falado aqui. E nada melhor que realizar os testes de performance na prática para convencer você, de que o CLR pode, em muitos casos, prover um grande ganho de performance nas suas consultas ao SQL Server.

Para gerar a massa de dados de testes, utilizei esse script:

IF (OBJECT_ID('dirceuresende.dbo.Teste_Performance') IS NOT NULL) DROP TABLE dirceuresende.dbo.Teste_Performance
CREATE TABLE dirceuresende.dbo.Teste_Performance (
    Id INT IDENTITY(1, 1),
    Nome VARCHAR(200),
    Numero INT,
    [Data] DATETIME,
    Observacao VARCHAR(MAX),
    CPF VARCHAR(11)
)

INSERT INTO dirceuresende.dbo.Teste_Performance ( Nome, Numero, [Data], Observacao, CPF )
SELECT 
    CAST(NEWID() AS VARCHAR(50)),
    dbo.fncRand(99999999),
    DATEADD(SECOND, (CAST(RAND() * 31536000 AS INT)), '2017-01-01') AS Dt_Venda,
    CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)),
    RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF


DECLARE @Contador INT = 1, @Total INT = 20


WHILE(@Contador <= @Total)
BEGIN
 
    INSERT INTO dirceuresende.dbo.Teste_Performance ( Nome, Numero, [Data], Observacao, CPF )
    SELECT 
        CAST(NEWID() AS VARCHAR(50)),
        dbo.fncRand(99999999),
        DATEADD(SECOND, (CAST(RAND() * 31536000 AS INT)), '2017-01-01') AS Dt_Venda,
        CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)),
        RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF
    FROM
        dirceuresende.dbo.Teste_Performance


    SET @Contador += 1
 

END

IF (OBJECT_ID('dirceuresende.dbo.Teste_Performance') IS NOT NULL) DROP TABLE dirceuresende.dbo.Teste_Performance

CREATE TABLE dirceuresende.dbo.Teste_Performance (

Id INT IDENTITY(1, 1),

Nome VARCHAR(200),

Numero INT,

[Data] DATETIME,

Observacao VARCHAR(MAX),

CPF VARCHAR(11)

)

INSERT INTO dirceuresende.dbo.Teste_Performance ( Nome, Numero, [Data], Observacao, CPF )

SELECT

CAST(NEWID() AS VARCHAR(50)),

dbo.fncRand(99999999),

DATEADD(SECOND, (CAST(RAND() * 31536000 AS INT)), '2017-01-01') AS Dt_Venda,

CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)),

RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF

DECLARE @Contador INT = 1, @Total INT = 20

WHILE(@Contador <= @Total)

BEGIN

INSERT INTO dirceuresende.dbo.Teste_Performance ( Nome, Numero, [Data], Observacao, CPF )

SELECT

CAST(NEWID() AS VARCHAR(50)),

dbo.fncRand(99999999),

DATEADD(SECOND, (CAST(RAND() * 31536000 AS INT)), '2017-01-01') AS Dt_Venda,

CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)) + ';' + CAST(NEWID() AS VARCHAR(50)),

RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF

FROM

dirceuresende.dbo.Teste_Performance

SET @Contador += 1

END

O código-fonte da função fncRand(), bem como a explicação de porque utilizo essa função ao invés da RAND(), você encontra no post SQL Server – Msg 443 Invalid use of a side-effecting operator ‘rand’ within a function.

fncPrimeiroDiaMes

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncPrimeiroDiaMes](@Dt_Referencia DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(DAY,-(DAY(@Dt_Referencia)-1), CAST(FLOOR(CAST(@Dt_Referencia AS FLOAT)) AS DATETIME))
END

CREATE FUNCTION [dbo].[fncPrimeiroDiaMes](@Dt_Referencia DATETIME)

RETURNS DATETIME

BEGIN

RETURN DATEADD(DAY,-(DAY(@Dt_Referencia)-1), CAST(FLOOR(CAST(@Dt_Referencia AS FLOAT)) AS DATETIME))

END

Código-fonte CLR:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime fncPrimeiro_Dia_Mes(SqlDateTime Dt_Referencia)
    {
        if (Dt_Referencia.IsNull)
            return SqlDateTime.Null;

        var data = Dt_Referencia.Value;
        return new DateTime(data.Year, data.Month, 1);
    }
}

using System;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlDateTime fncPrimeiro_Dia_Mes(SqlDateTime Dt_Referencia)

{

if (Dt_Referencia.IsNull)

return SqlDateTime.Null;

var data = Dt_Referencia.Value;

return new DateTime(data.Year, data.Month, 1);

}

Saída:

Comparação de performance no SELECT

Comparação de performance no WHERE

fncUltimoDiaMes

Visualizar conteúdo

Código-fonte T-SQL:

create function [dbo].[fncUltimoDiaMes](@Dt_Referencia datetime)
RETURNS datetime
as
begin
    return dateadd(day,-1,dateadd(month,+1,DATEADD(DAY,-(DAY(@Dt_Referencia)-1), CAST(FLOOR(CAST(@Dt_Referencia AS FLOAT)) AS DATETIME))))
end

create function [dbo].[fncUltimoDiaMes](@Dt_Referencia datetime)

RETURNS datetime

begin

return dateadd(day,-1,dateadd(month,+1,DATEADD(DAY,-(DAY(@Dt_Referencia)-1), CAST(FLOOR(CAST(@Dt_Referencia AS FLOAT)) AS DATETIME))))

end

Código-fonte CLR:

using System;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime fncUltimo_Dia_Mes(DateTime data)
    {
        return new DateTime(data.Year, data.Month, DateTime.DaysInMonth(data.Year, data.Month));
    }
}

using System;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static DateTime fncUltimo_Dia_Mes(DateTime data)

{

return new DateTime(data.Year, data.Month, DateTime.DaysInMonth(data.Year, data.Month));

}

Saída:

Comparação de performance no SELECT

Comparação de performance no WHERE

fncSplit

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncSplit] ( @String varchar(8000), @Separador varchar(8000), @PosBusca int )
RETURNS varchar(8000)
AS BEGIN
    
    DECLARE @Index int, @Max int, @Retorno varchar(8000)

    DECLARE @Partes as TABLE ( Id_Parte int identity(1,1), Texto varchar(8000) )

    SET @Index = charIndex(@Separador,@String)

    WHILE (@Index > 0) BEGIN	
        INSERT INTO @Partes SELECT SubString(@String,1,@Index-1)
        SET @String = Rtrim(Ltrim(SubString(@String,@Index+Len(@Separador),Len(@String))))
        SET @Index = charIndex(@Separador,@String)
    END

    IF (@String != '') INSERT INTO @Partes SELECT @String

    SELECT @Max = Count(*) FROM @Partes

    IF (@PosBusca = 0) SET @Retorno = Cast(@Max as varchar(5))
    IF (@PosBusca < 0) SET @PosBusca = @Max + 1 + @PosBusca
    IF (@PosBusca > 0) SELECT @Retorno = Texto FROM @Partes WHERE Id_Parte = @PosBusca

    RETURN Rtrim(Ltrim(@Retorno))

END

CREATE FUNCTION [dbo].[fncSplit] ( @String varchar(8000), @Separador varchar(8000), @PosBusca int )

RETURNS varchar(8000)

AS BEGIN

DECLARE @Index int, @Max int, @Retorno varchar(8000)

DECLARE @Partes as TABLE ( Id_Parte int identity(1,1), Texto varchar(8000) )

SET @Index = charIndex(@Separador,@String)

WHILE (@Index > 0) BEGIN

INSERT INTO @Partes SELECT SubString(@String,1,@Index-1)

SET @String = Rtrim(Ltrim(SubString(@String,@Index+Len(@Separador),Len(@String))))

SET @Index = charIndex(@Separador,@String)

END

IF (@String != '') INSERT INTO @Partes SELECT @String

SELECT @Max = Count(*) FROM @Partes

IF (@PosBusca = 0) SET @Retorno = Cast(@Max as varchar(5))

IF (@PosBusca < 0) SET @PosBusca = @Max + 1 + @PosBusca

IF (@PosBusca > 0) SELECT @Retorno = Texto FROM @Partes WHERE Id_Parte = @PosBusca

RETURN Rtrim(Ltrim(@Retorno))

END

Código-fonte CLR:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncSplit(SqlString Ds_Palavra, SqlString Ds_Delimitador, SqlInt32 Qt_Indice)
    {

        if (Ds_Palavra.IsNull || Qt_Indice.IsNull)
            return SqlString.Null;

        if (Ds_Delimitador.IsNull)
            return Ds_Palavra.Value;

        var indice = (Qt_Indice.Value == 0) ? 1 : Qt_Indice.Value;
        var palavra = Ds_Palavra.Value.Split(Ds_Delimitador.Value.ToCharArray());

        if (Qt_Indice.Value == 0)
            return palavra.Length.ToString();

        if (palavra.Length < Qt_Indice.Value)
            return SqlString.Null;

        return indice > 0 ? palavra[indice - 1] : palavra[palavra.Length - Math.Abs(indice)];

    }
}

using System;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncSplit(SqlString Ds_Palavra, SqlString Ds_Delimitador, SqlInt32 Qt_Indice)

{

if (Ds_Palavra.IsNull || Qt_Indice.IsNull)

return SqlString.Null;

if (Ds_Delimitador.IsNull)

return Ds_Palavra.Value;

var indice = (Qt_Indice.Value == 0) ? 1 : Qt_Indice.Value;

var palavra = Ds_Palavra.Value.Split(Ds_Delimitador.Value.ToCharArray());

if (Qt_Indice.Value == 0)

return palavra.Length.ToString();

if (palavra.Length < Qt_Indice.Value)

return SqlString.Null;

return indice > 0 ? palavra[indice - 1] : palavra[palavra.Length - Math.Abs(indice)];

}

Saída:

Comparação de performance no SELECT
Reparem que, após esperar 5 mins, acabei cancelando e iniciando os testes novamente, mas com apenas 10.000 registros para a função T-SQL, comparando com a função do CLR rodando em 100.000 registros. Mas mesmo a função do CLR sendo executada 10x mais, vejam o que aconteceu nos resultados…

Comparação de performance no WHERE

fncBase64_Encode

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncBase64_Encode] (
    @string VARCHAR(MAX)
) 
RETURNS VARCHAR(MAX)
AS BEGIN

    DECLARE 
        @source VARBINARY(MAX), 
        @encoded VARCHAR(MAX)
        
    set @source = convert(varbinary(max), @string)
    SET @encoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

    RETURN @encoded

END

CREATE FUNCTION [dbo].[fncBase64_Encode] (

@string VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS BEGIN

DECLARE

@source VARBINARY(MAX),

@encoded VARCHAR(MAX)

set @source = convert(varbinary(max), @string)

SET @encoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

RETURN @encoded

END

Código-fonte CLR:

using System;
using System.Data.SqlTypes;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncBase64_Encode(SqlString Ds_Texto)
    {
        return Ds_Texto.IsNull ? null : Convert.ToBase64String(Encoding.UTF8.GetBytes(Ds_Texto.Value));
    }
}

using System;

using System.Data.SqlTypes;

using System.Text;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncBase64_Encode(SqlString Ds_Texto)

{

return Ds_Texto.IsNull ? null : Convert.ToBase64String(Encoding.UTF8.GetBytes(Ds_Texto.Value));

}

Saída:

Comparação de performance no SELECT

fncBase64_Decode

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncBase64_Decode] (
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS BEGIN

    DECLARE @decoded VARCHAR(MAX)
    SET @decoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@string"))', 'varbinary(max)')

    RETURN convert(varchar(max), @decoded)
    
END

CREATE FUNCTION [dbo].[fncBase64_Decode] (

@string VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS BEGIN

DECLARE @decoded VARCHAR(MAX)

SET @decoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@string"))', 'varbinary(max)')

RETURN convert(varchar(max), @decoded)

END

Código-fonte CLR:

using System;
using System.Data.SqlTypes;
using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncBase64_Decode(SqlString Ds_Texto)
    {
        return Ds_Texto.IsNull ? null : Encoding.UTF8.GetString(Convert.FromBase64String(Ds_Texto.Value));
    }
}

using System;

using System.Data.SqlTypes;

using System.Text;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncBase64_Decode(SqlString Ds_Texto)

{

return Ds_Texto.IsNull ? null : Encoding.UTF8.GetString(Convert.FromBase64String(Ds_Texto.Value));

}

Saída:

Comparação de performance no SELECT

fncValida_CPF

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncValida_CPF](@Nr_Documento [varchar](11))
RETURNS [bit]
AS 
BEGIN

    
    IF (ISNUMERIC(@Nr_Documento) = 0)
        RETURN 0


    DECLARE
        @Contador_1 INT,
        @Contador_2 INT,
        @Digito_1 INT,
        @Digito_2 INT,
        @Nr_Documento_Aux VARCHAR(11)

    SET @Nr_Documento_Aux = LTRIM(RTRIM(@Nr_Documento))
    SET @Digito_1 = 0

    IF LEN(@Nr_Documento_Aux) <> 11
        RETURN 0
    ELSE
    BEGIN

        -- Cálculo do segundo dígito
        SET @Nr_Documento_Aux = SUBSTRING(@Nr_Documento_Aux, 1, 9)

        SET @Contador_1 = 2

        WHILE @Contador_1 <= 10
        BEGIN 
            SET @Digito_1 = @Digito_1 + ( @Contador_1 * CAST(SUBSTRING(@Nr_Documento_Aux, 11 - @Contador_1, 1) AS INT) )
            SET @Contador_1 = @Contador_1 + 1
        END 

        SET @Digito_1 = @Digito_1 - ( @Digito_1 / 11 ) * 11

        IF @Digito_1 <= 1
            SET @Digito_1 = 0
        ELSE
            SET @Digito_1 = 11 - @Digito_1

        SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_1 AS VARCHAR(1))

        IF @Nr_Documento_Aux <> SUBSTRING(@Nr_Documento, 1, 10)
            RETURN 0
        ELSE 
        BEGIN 

            -- Cálculo do segundo dígito
            SET @Digito_2 = 0
            SET @Contador_2 = 2

            WHILE (@Contador_2 <= 11)
            BEGIN 
                SET @Digito_2 = @Digito_2 + ( @Contador_2 * CAST(SUBSTRING(@Nr_Documento_Aux, 12 - @Contador_2, 1) AS INT) )
                SET @Contador_2 = @Contador_2 + 1
            END 

            SET @Digito_2 = @Digito_2 - ( @Digito_2 / 11 ) * 11


            IF @Digito_2 < 2
                SET @Digito_2 = 0
            ELSE
                SET @Digito_2 = 11 - @Digito_2
    

            SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_2 AS VARCHAR(1))


            IF @Nr_Documento_Aux <> @Nr_Documento
                RETURN 0

        END
                    
    END

    RETURN 1

END

CREATE FUNCTION [dbo].[fncValida_CPF](@Nr_Documento [varchar](11))

RETURNS [bit]

BEGIN

IF (ISNUMERIC(@Nr_Documento) = 0)

RETURN 0

DECLARE

@Contador_1 INT,

@Contador_2 INT,

@Digito_1 INT,

@Digito_2 INT,

@Nr_Documento_Aux VARCHAR(11)

SET @Nr_Documento_Aux = LTRIM(RTRIM(@Nr_Documento))

SET @Digito_1 = 0

IF LEN(@Nr_Documento_Aux) <> 11

RETURN 0

ELSE

BEGIN

-- Cálculo do segundo dígito

SET @Nr_Documento_Aux = SUBSTRING(@Nr_Documento_Aux, 1, 9)

SET @Contador_1 = 2

WHILE @Contador_1 <= 10

BEGIN

SET @Digito_1 = @Digito_1 + ( @Contador_1 * CAST(SUBSTRING(@Nr_Documento_Aux, 11 - @Contador_1, 1) AS INT) )

SET @Contador_1 = @Contador_1 + 1

END

SET @Digito_1 = @Digito_1 - ( @Digito_1 / 11 ) * 11

IF @Digito_1 <= 1

SET @Digito_1 = 0

ELSE

SET @Digito_1 = 11 - @Digito_1

SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_1 AS VARCHAR(1))

IF @Nr_Documento_Aux <> SUBSTRING(@Nr_Documento, 1, 10)

RETURN 0

ELSE

BEGIN

-- Cálculo do segundo dígito

SET @Digito_2 = 0

SET @Contador_2 = 2

WHILE (@Contador_2 <= 11)

BEGIN

SET @Digito_2 = @Digito_2 + ( @Contador_2 * CAST(SUBSTRING(@Nr_Documento_Aux, 12 - @Contador_2, 1) AS INT) )

SET @Contador_2 = @Contador_2 + 1

END

SET @Digito_2 = @Digito_2 - ( @Digito_2 / 11 ) * 11

IF @Digito_2 < 2

SET @Digito_2 = 0

ELSE

SET @Digito_2 = 11 - @Digito_2

SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_2 AS VARCHAR(1))

IF @Nr_Documento_Aux <> @Nr_Documento

RETURN 0

END

RETURN 1

END

Código-fonte CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fncValida_CPF(SqlString Ds_CPF)
    {

        if (Ds_CPF.IsNull)
            return false;


        var valor = Ds_CPF.Value.Replace(".", "").Replace("-", "").Replace(" ", "");


        if (valor.Length != 11)
            return false;


        long resultadoConversaoInt;
        if (!long.TryParse(valor, out resultadoConversaoInt))
            return false;


        var igual = true;
        for (var i = 1; i < 11 && igual; i++)
            if (valor[i] != valor[0])
                igual = false;

        if (igual || valor == "12345678909") return false;


        var numeros = new int[11];
        for (var i = 0; i < 11; i++)
            numeros[i] = int.Parse(valor[i].ToString());


        var soma = 0;
        for (var i = 0; i < 9; i++)
            soma += (10 - i) * numeros[i];


        var resultado = soma % 11;
        if (resultado == 1 || resultado == 0)
        {
            if (numeros[9] != 0)
                return false;
        }
        else if (numeros[9] != 11 - resultado)
            return false;


        soma = 0;
        for (var i = 0; i < 10; i++)
            soma += (11 - i) * numeros[i];


        resultado = soma % 11;
        if (resultado == 1 || resultado == 0)
        {
            if (numeros[10] != 0)
                return false;
        }
        else if (numeros[10] != 11 - resultado)
            return false;


        return true;

    }

}

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean fncValida_CPF(SqlString Ds_CPF)

{

if (Ds_CPF.IsNull)

return false;

var valor = Ds_CPF.Value.Replace(".", "").Replace("-", "").Replace(" ", "");

if (valor.Length != 11)

return false;

long resultadoConversaoInt;

if (!long.TryParse(valor, out resultadoConversaoInt))

return false;

var igual = true;

for (var i = 1; i < 11 && igual; i++)

if (valor[i] != valor[0])

igual = false;

if (igual || valor == "12345678909") return false;

var numeros = new int[11];

for (var i = 0; i < 11; i++)

numeros[i] = int.Parse(valor[i].ToString());

var soma = 0;

for (var i = 0; i < 9; i++)

soma += (10 - i) * numeros[i];

var resultado = soma % 11;

if (resultado == 1 || resultado == 0)

{

if (numeros[9] != 0)

return false;

}

else if (numeros[9] != 11 - resultado)

return false;

soma = 0;

for (var i = 0; i < 10; i++)

soma += (11 - i) * numeros[i];

resultado = soma % 11;

if (resultado == 1 || resultado == 0)

{

if (numeros[10] != 0)

return false;

}

else if (numeros[10] != 11 - resultado)

return false;

return true;

}

Saída:

Comparação de performance no SELECT

fncRecupera_Numeros

Visualizar conteúdo

Código-fonte T-SQL:

CREATE function [dbo].[fncRecupera_Numeros](@str varchar(500))  
returns varchar(500)  
begin  

    declare @startingIndex int  
    set @startingIndex=0  
    while 1=1  
    begin  
        set @startingIndex= patindex('%[^0-9]%',@str)  
        if @startingIndex <> 0  
        begin  
            set @str = replace(@str,substring(@str,@startingIndex,1),'')  
        end  
        else    break;   
    end  

    return NULLIF(@str, '')

end

CREATE function [dbo].[fncRecupera_Numeros](@str varchar(500))

returns varchar(500)

begin

declare @startingIndex int

set @startingIndex=0

while 1=1

begin

set @startingIndex= patindex('%[^0-9]%',@str)

if @startingIndex <> 0

begin

set @str = replace(@str,substring(@str,@startingIndex,1),'')

end

else break;

end

return NULLIF(@str, '')

end

Código-fonte CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncRecupera_Numeros(SqlString Ds_Texto)
    {


        if (Ds_Texto.IsNull)
            return SqlString.Null;


        var retorno = "";
        var palavra = Ds_Texto.Value;
        var len = palavra.Length;

        for (var i = 0; i < len; ++i)
        {
            var letra = palavra[i];

            if (letra >= '0' && letra <= '9')
                retorno += letra;
        }

        return string.IsNullOrEmpty(retorno) ? SqlString.Null : retorno;
    }
};

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncRecupera_Numeros(SqlString Ds_Texto)

{

if (Ds_Texto.IsNull)

return SqlString.Null;

var retorno = "";

var palavra = Ds_Texto.Value;

var len = palavra.Length;

for (var i = 0; i < len; ++i)

{

var letra = palavra[i];

if (letra >= '0' && letra <= '9')

retorno += letra;

}

return string.IsNullOrEmpty(retorno) ? SqlString.Null : retorno;

}

};

Saída:

Comparação de performance no SELECT

fncMes

Visualizar conteúdo

Código-fonte T-SQL:

-- @Fl_Tipo define como é o tipo de formatação
-- 1: Janeiro Fevereiro Marco Abril...
-- 2: JANEIRO FEVEVEIRO MARCO ABRIL
-- 3: Jan Fev Mar Abr
-- 4: JAN FEV MAR ABR
-- 5: January, February ... (cubo)

CREATE FUNCTION [dbo].[fncMes] (@Dt_Referencia DATETIME, @Fl_Tipo TINYINT, @Fl_Incluir_Ano BIT = 0, @Fl_Incluir_Dia BIT = 0)
RETURNS VARCHAR(30)
AS BEGIN

    
    DECLARE @Mes TINYINT
    SET @Mes = DATEPART(MONTH, @Dt_Referencia)


    DECLARE @Ds_Mes as varchar(30)
    SET @Ds_Mes = CASE 
        WHEN @Mes =  1 THEN 'Janeiro'
        WHEN @Mes =  2 THEN 'Fevereiro'
        WHEN @Mes =  3 THEN 'Março'
        WHEN @Mes =  4 THEN 'Abril'
        WHEN @Mes =  5 THEN 'Maio'
        WHEN @Mes =  6 THEN 'Junho'
        WHEN @Mes =  7 THEN 'Julho'
        WHEN @Mes =  8 THEN 'Agosto'
        WHEN @Mes =  9 THEN 'Setembro'
        WHEN @Mes = 10 THEN 'Outubro'
        WHEN @Mes = 11 THEN 'Novembro'
        WHEN @Mes = 12 THEN 'Dezembro'	
        ELSE NULL
    END
    IF (@Fl_Tipo IN (3,4)) SET @Ds_Mes = SubString(@Ds_Mes,1,3)
    IF (@Fl_Tipo IN (2,4)) SET @Ds_Mes = Upper(@Ds_Mes)

    IF (@Fl_Tipo = 5) BEGIN
        DECLARE @Date datetime 
        SET @Date = '2001'+Right('0'+Cast(@Mes as varchar(2)),2)+'01' 
        SET @Ds_Mes = DateName(Month,@Date)
    END
    
    
    IF (@Fl_Incluir_Ano = 1)
        SET @Ds_Mes = @Ds_Mes + ' ' + CAST(DATEPART(YEAR, @Dt_Referencia) AS VARCHAR(4))
    
    
    IF (@Fl_Incluir_Dia = 1)
        SET @Ds_Mes =   CAST(DATEPART(DAY, @Dt_Referencia) AS VARCHAR(4)) + '/' + @Ds_Mes
    

    RETURN @Ds_Mes
    
END

-- @Fl_Tipo define como é o tipo de formatação

-- 1: Janeiro Fevereiro Marco Abril...

-- 2: JANEIRO FEVEVEIRO MARCO ABRIL

-- 3: Jan Fev Mar Abr

-- 4: JAN FEV MAR ABR

-- 5: January, February ... (cubo)

CREATE FUNCTION [dbo].[fncMes] (@Dt_Referencia DATETIME, @Fl_Tipo TINYINT, @Fl_Incluir_Ano BIT = 0, @Fl_Incluir_Dia BIT = 0)

RETURNS VARCHAR(30)

AS BEGIN

DECLARE @Mes TINYINT

SET @Mes = DATEPART(MONTH, @Dt_Referencia)

DECLARE @Ds_Mes as varchar(30)

SET @Ds_Mes = CASE

WHEN @Mes = 1 THEN 'Janeiro'

WHEN @Mes = 2 THEN 'Fevereiro'

WHEN @Mes = 3 THEN 'Março'

WHEN @Mes = 4 THEN 'Abril'

WHEN @Mes = 5 THEN 'Maio'

WHEN @Mes = 6 THEN 'Junho'

WHEN @Mes = 7 THEN 'Julho'

WHEN @Mes = 8 THEN 'Agosto'

WHEN @Mes = 9 THEN 'Setembro'

WHEN @Mes = 10 THEN 'Outubro'

WHEN @Mes = 11 THEN 'Novembro'

WHEN @Mes = 12 THEN 'Dezembro'

ELSE NULL

END

IF (@Fl_Tipo IN (3,4)) SET @Ds_Mes = SubString(@Ds_Mes,1,3)

IF (@Fl_Tipo IN (2,4)) SET @Ds_Mes = Upper(@Ds_Mes)

IF (@Fl_Tipo = 5) BEGIN

DECLARE @Date datetime

SET @Date = '2001'+Right('0'+Cast(@Mes as varchar(2)),2)+'01'

SET @Ds_Mes = DateName(Month,@Date)

END

IF (@Fl_Incluir_Ano = 1)

SET @Ds_Mes = @Ds_Mes + ' ' + CAST(DATEPART(YEAR, @Dt_Referencia) AS VARCHAR(4))

IF (@Fl_Incluir_Dia = 1)

SET @Ds_Mes = CAST(DATEPART(DAY, @Dt_Referencia) AS VARCHAR(4)) + '/' + @Ds_Mes

RETURN @Ds_Mes

END

Código-fonte CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncMes(SqlDateTime Dt_Referencia, SqlInt32 Fl_Tipo, SqlBoolean Fl_Incluir_Ano, SqlBoolean Fl_Incluir_Dia)
    {


        #region Validações

        if (Dt_Referencia.IsNull)
            return null;


        if (Fl_Tipo.IsNull)
            Fl_Tipo = 1;


        if (Fl_Incluir_Ano.IsNull)
            Fl_Incluir_Ano = false;


        if (Fl_Incluir_Dia.IsNull)
            Fl_Incluir_Dia = false;

        #endregion


        var mes = Dt_Referencia.Value.Month;
        string retorno;


        if (Fl_Tipo == 5)
        {

            switch (mes)
            {
                case 1:
                    retorno = "January";
                    break;

                case 2:
                    retorno = "February";
                    break;

                case 3:
                    retorno = "March";
                    break;

                case 4:
                    retorno = "April";
                    break;

                case 5:
                    retorno = "May";
                    break;

                case 6:
                    retorno = "June";
                    break;

                case 7:
                    retorno = "July";
                    break;

                case 8:
                    retorno = "August";
                    break;

                case 9:
                    retorno = "September";
                    break;

                case 10:
                    retorno = "October";
                    break;

                case 11:
                    retorno = "November";
                    break;

                case 12:
                    retorno = "December";
                    break;

                default:
                    retorno = null;
                    break;
            }
               
        }
        else
        {

            switch (mes)
            {
                case 1:
                    retorno = "Janeiro";
                    break;

                case 2:
                    retorno = "Fevereiro";
                    break;

                case 3:
                    retorno = "Março";
                    break;

                case 4:
                    retorno = "Abril";
                    break;

                case 5:
                    retorno = "Maio";
                    break;

                case 6:
                    retorno = "Junho";
                    break;

                case 7:
                    retorno = "Julho";
                    break;

                case 8:
                    retorno = "Agosto";
                    break;

                case 9:
                    retorno = "Setembro";
                    break;

                case 10:
                    retorno = "Outubro";
                    break;

                case 11:
                    retorno = "Novembro";
                    break;

                case 12:
                    retorno = "Dezembro";
                    break;

                default:
                    retorno = null;
                    break;
            }

            if (Fl_Tipo == 3 || Fl_Tipo == 4)
                retorno = retorno?.Substring(0, 3);

            if (Fl_Tipo == 2 || Fl_Tipo == 4)
                retorno = retorno?.ToUpper();

        }

        
        if (Fl_Incluir_Ano.Value)
            retorno += " " + Dt_Referencia.Value.Year.ToString("0000");


        if (Fl_Incluir_Dia.Value)
            retorno = Dt_Referencia.Value.Day.ToString("00") + "/" + retorno;


        return retorno;

    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncMes(SqlDateTime Dt_Referencia, SqlInt32 Fl_Tipo, SqlBoolean Fl_Incluir_Ano, SqlBoolean Fl_Incluir_Dia)

{

#region Validações

if (Dt_Referencia.IsNull)

return null;

if (Fl_Tipo.IsNull)

Fl_Tipo = 1;

if (Fl_Incluir_Ano.IsNull)

Fl_Incluir_Ano = false;

if (Fl_Incluir_Dia.IsNull)

Fl_Incluir_Dia = false;

#endregion

var mes = Dt_Referencia.Value.Month;

string retorno;

if (Fl_Tipo == 5)

{

switch (mes)

{

case 1:

retorno = "January";

break;

case 2:

retorno = "February";

break;

case 3:

retorno = "March";

break;

case 4:

retorno = "April";

break;

case 5:

retorno = "May";

break;

case 6:

retorno = "June";

break;

case 7:

retorno = "July";

break;

case 8:

retorno = "August";

break;

case 9:

retorno = "September";

break;

case 10:

retorno = "October";

break;

case 11:

retorno = "November";

break;

case 12:

retorno = "December";

break;

default:

retorno = null;

break;

}

else

{

switch (mes)

{

case 1:

retorno = "Janeiro";

break;

case 2:

retorno = "Fevereiro";

break;

case 3:

retorno = "Março";

break;

case 4:

retorno = "Abril";

break;

case 5:

retorno = "Maio";

break;

case 6:

retorno = "Junho";

break;

case 7:

retorno = "Julho";

break;

case 8:

retorno = "Agosto";

break;

case 9:

retorno = "Setembro";

break;

case 10:

retorno = "Outubro";

break;

case 11:

retorno = "Novembro";

break;

case 12:

retorno = "Dezembro";

break;

default:

retorno = null;

break;

}

if (Fl_Tipo == 3 || Fl_Tipo == 4)

retorno = retorno?.Substring(0, 3);

if (Fl_Tipo == 2 || Fl_Tipo == 4)

retorno = retorno?.ToUpper();

}

if (Fl_Incluir_Ano.Value)

retorno += " " + Dt_Referencia.Value.Year.ToString("0000");

if (Fl_Incluir_Dia.Value)

retorno = Dt_Referencia.Value.Day.ToString("00") + "/" + retorno;

return retorno;

}

Saída:

Comparação de performance no SELECT

fncConverte_Em_Horas

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncConverte_Em_Horas] (@Qt_Tempo BIGINT, @Tp_Tempo VARCHAR(10))
RETURNS VARCHAR(MAX)
BEGIN 

    DECLARE @ResultadoNegativo TINYINT = 0

    IF (@Qt_Tempo < 0)
    BEGIN
        SET @ResultadoNegativo = 1
        SET @Qt_Tempo = @Qt_Tempo * (-1)
    END 
    

    DECLARE @Diferenca BIGINT = @Qt_Tempo, 
            @Segundos BIGINT = 0, 
            @Minutos BIGINT = 0, 
            @Horas BIGINT = 0

    
    IF(@Tp_Tempo IN('ss','second'))
    BEGIN
        
        SET @Horas = @Diferenca / 3600 
        SET @Diferenca = @Diferenca - (@Horas * 3600) 

        SET @Minutos = @Diferenca / 60 
        SET @Diferenca = @Diferenca - (@Minutos * 60) 

        SET @Segundos = @Diferenca 
    
    END
    
    IF(@Tp_Tempo IN('mm','minute'))
    BEGIN
        
        SET @Horas = @Diferenca / 60
        SET @Diferenca = @Diferenca - (@Horas * 60)

        SET @Minutos = @Diferenca

        SET @Segundos = 0 
    
    END
    
    IF(@Tp_Tempo IN('hh','hour'))
    BEGIN
        
        SET @Horas = @Diferenca 

        SET @Minutos = 0

        SET @Segundos = 0
    
    END
    
        
    RETURN 
        (CASE WHEN @ResultadoNegativo = 1 THEN '-' ELSE '' END) + 
        (CASE WHEN @Horas <= 9 THEN RIGHT('00' + CAST(@Horas AS VARCHAR(1)), 2) ELSE CAST(@Horas AS VARCHAR(MAX)) END + ':' + 
         RIGHT('00' + CAST(@Minutos AS VARCHAR(2)), 2) + ':' + 
         RIGHT('00' + CAST(@Segundos AS VARCHAR(2)), 2)) 
        
END

CREATE FUNCTION [dbo].[fncConverte_Em_Horas] (@Qt_Tempo BIGINT, @Tp_Tempo VARCHAR(10))

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @ResultadoNegativo TINYINT = 0

IF (@Qt_Tempo < 0)

BEGIN

SET @ResultadoNegativo = 1

SET @Qt_Tempo = @Qt_Tempo * (-1)

END

DECLARE @Diferenca BIGINT = @Qt_Tempo,

@Segundos BIGINT = 0,

@Minutos BIGINT = 0,

@Horas BIGINT = 0

IF(@Tp_Tempo IN('ss','second'))

BEGIN

SET @Horas = @Diferenca / 3600

SET @Diferenca = @Diferenca - (@Horas * 3600)

SET @Minutos = @Diferenca / 60

SET @Diferenca = @Diferenca - (@Minutos * 60)

SET @Segundos = @Diferenca

END

IF(@Tp_Tempo IN('mm','minute'))

BEGIN

SET @Horas = @Diferenca / 60

SET @Diferenca = @Diferenca - (@Horas * 60)

SET @Minutos = @Diferenca

SET @Segundos = 0

END

IF(@Tp_Tempo IN('hh','hour'))

BEGIN

SET @Horas = @Diferenca

SET @Minutos = 0

SET @Segundos = 0

END

RETURN

(CASE WHEN @ResultadoNegativo = 1 THEN '-' ELSE '' END) +

(CASE WHEN @Horas <= 9 THEN RIGHT('00' + CAST(@Horas AS VARCHAR(1)), 2) ELSE CAST(@Horas AS VARCHAR(MAX)) END + ':' +

RIGHT('00' + CAST(@Minutos AS VARCHAR(2)), 2) + ':' +

RIGHT('00' + CAST(@Segundos AS VARCHAR(2)), 2))

END

Código-fonte CLR:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncConverte_Em_Horas(SqlInt64 Qt_Tempo, SqlString Tp_Tempo)
    {

        if (Qt_Tempo.IsNull)
            return SqlString.Null;


        if (Tp_Tempo.IsNull)
            return SqlString.Null;


        var resultadoNegativo = false;
        var tempo = Qt_Tempo.Value;

        if (tempo < 0)
        {
            resultadoNegativo = true;
            tempo = tempo * -1;
        }


        var diferenca = tempo;
        long segundos = 0;
        long minutos = 0;
        long horas = 0;

        switch (Tp_Tempo.Value)
        {

            case "ss":
            case "second":
                horas = diferenca / 3600;
                diferenca -= (horas * 3600);

                minutos = diferenca / 60;
                diferenca -= (minutos * 60);

                segundos = diferenca;

                break;


            case "mm":
            case "minute":
                horas = diferenca / 60;
                diferenca -= (horas * 60);

                minutos = diferenca;

                break;


            case "hh":
            case "hour":
                horas = diferenca;

                break;

        }

        return ((resultadoNegativo) ? "-" : "") + horas.ToString("00") + ":" + minutos.ToString("00") + ":" + segundos.ToString("00");

    }
}

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncConverte_Em_Horas(SqlInt64 Qt_Tempo, SqlString Tp_Tempo)

{

if (Qt_Tempo.IsNull)

return SqlString.Null;

if (Tp_Tempo.IsNull)

return SqlString.Null;

var resultadoNegativo = false;

var tempo = Qt_Tempo.Value;

if (tempo < 0)

{

resultadoNegativo = true;

tempo = tempo * -1;

}

var diferenca = tempo;

long segundos = 0;

long minutos = 0;

long horas = 0;

switch (Tp_Tempo.Value)

{

case "ss":

case "second":

horas = diferenca / 3600;

diferenca -= (horas * 3600);

minutos = diferenca / 60;

diferenca -= (minutos * 60);

segundos = diferenca;

break;

case "mm":

case "minute":

horas = diferenca / 60;

diferenca -= (horas * 60);

minutos = diferenca;

break;

case "hh":

case "hour":

horas = diferenca;

break;

}

return ((resultadoNegativo) ? "-" : "") + horas.ToString("00") + ":" + minutos.ToString("00") + ":" + segundos.ToString("00");

}

Saída:

Comparação de performance no SELECT

fncFormata_Documento

Visualizar conteúdo

Código-fonte T-SQL:

CREATE FUNCTION [dbo].[fncFormata_Documento](
    @Nr_Documento varchar(max)
)
RETURNS varchar(max)
AS BEGIN

    SET @Nr_Documento = Replace(@Nr_Documento,'.','')
    SET @Nr_Documento = Replace(@Nr_Documento,'/','')
    SET @Nr_Documento = Replace(@Nr_Documento,'-','')

    DECLARE @Nr_Formatado varchar(max)
    
    
    IF (LEN(@Nr_Documento) = 14) BEGIN
        SET @Nr_Formatado = 
            substring(@Nr_Documento,1,2) + '.' + 
            substring(@Nr_Documento,3,3) + '.' + 
            substring(@Nr_Documento,6,3) + '/' + 
            substring(@Nr_Documento,9,4) + '-' + 
            substring(@Nr_Documento,13,2)
    END
    
    
    IF (LEN(@Nr_Documento) = 11) BEGIN	
        SET @Nr_Formatado = 
            substring(@Nr_Documento,1,3) + '.' + 
            substring(@Nr_Documento,4,3) + '.' + 
            substring(@Nr_Documento,7,3) + '-' + 
            substring(@Nr_Documento,10,2)
    END
    
    IF (@Nr_Formatado IS NULL) SET @Nr_Formatado = @Nr_Documento
    
    RETURN @Nr_Formatado
    
END

CREATE FUNCTION [dbo].[fncFormata_Documento](

@Nr_Documento varchar(max)

)

RETURNS varchar(max)

AS BEGIN

SET @Nr_Documento = Replace(@Nr_Documento,'.','')

SET @Nr_Documento = Replace(@Nr_Documento,'/','')

SET @Nr_Documento = Replace(@Nr_Documento,'-','')

DECLARE @Nr_Formatado varchar(max)

IF (LEN(@Nr_Documento) = 14) BEGIN

SET @Nr_Formatado =

substring(@Nr_Documento,1,2) + '.' +

substring(@Nr_Documento,3,3) + '.' +

substring(@Nr_Documento,6,3) + '/' +

substring(@Nr_Documento,9,4) + '-' +

substring(@Nr_Documento,13,2)

END

IF (LEN(@Nr_Documento) = 11) BEGIN

SET @Nr_Formatado =

substring(@Nr_Documento,1,3) + '.' +

substring(@Nr_Documento,4,3) + '.' +

substring(@Nr_Documento,7,3) + '-' +

substring(@Nr_Documento,10,2)

END

IF (@Nr_Formatado IS NULL) SET @Nr_Formatado = @Nr_Documento

RETURN @Nr_Formatado

END

Código-fonte CLR:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncFormata_Documento(SqlString Nr_Documento)
    {

        if (Nr_Documento.IsNull)
            return SqlString.Null;


        var documento = Nr_Documento.Value.Replace(".", "").Replace("-", "").Replace("/", "").Replace(" ", "");

        switch (documento.Length)
        {

            case 11:
                return Convert.ToUInt64(documento).ToString(@"000\.000\.000\-00");

            case 14:
                return Convert.ToUInt64(documento).ToString(@"00\.000\.000\/0000\-00");

        }

        return documento;

    }

}

using System;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncFormata_Documento(SqlString Nr_Documento)

{

if (Nr_Documento.IsNull)

return SqlString.Null;

var documento = Nr_Documento.Value.Replace(".", "").Replace("-", "").Replace("/", "").Replace(" ", "");

switch (documento.Length)

{

case 11:

return Convert.ToUInt64(documento).ToString(@"000\.000\.000\-00");

case 14:

return Convert.ToUInt64(documento).ToString(@"00\.000\.000\/0000\-00");

}

return documento;

}

Saída:

Comparação de performance no SELECT

Conclusão

Após 10 testes de performance entre Scalar functions T-SQL x CLR, pode-se chegar a conclusão que em todos os casos apresentados (escolhi 10 de 90 funções que tenho nas 2 linguagens, aleatoriamente), a função CLR teve um enorme ganho de performance, tanto de CPU quanto de tempo de execução. Na empresa onde atuo como DBA, fiz o teste em todas as 90 funções e em 87 casos, a função escrita em C# foi executada em, no mínimo, a metade do tempo da função T-SQL.

Entretanto, conforme já falei acima, isso não quer dizer que em 100% dos casos isso irá ocorrer. Como tudo relacionado à performance tuning, não existe uma dica perfeita que se aplica a 100% das situações. Antes de alterar uma função T-SQL para SQL, faça vários testes para garantir que realmente houve um ganho expressivo de performance ao criar a função no CLR.

Nos primeiros 2 exemplos, onde as funções são bem simples, pude comparar também com as funções nativas do SQL Server, sem o uso de funções UDF. Neste casos, foi possível observar que sem utilizar função UDF e aplicando as funções nativas diretamente no SELECT e/ou WHERE, ela acaba sendo mais rápida até que o CLR. Entretanto, muitas vezes, as funções são bastante complexas, o que acaba dificultando e até impossibilitando o uso das mesmas sem a criação de uma função UDF.

As funções no SQL Server são uma ótima forma de encapsular e reaproveitar código, mas ao mesmo tempo, podem acabar comprometendo a performance de suas consultas. Neste caso, a solução que atua como um meio termo entre função nativa do SQL Server e função escalar T-SQL seria a função escalar do CLR, que possui uma performance muito superior à função T-SQL (na grande maioria dos casos) e próxima da função nativa e ao mesmo tempo, possibilita o reaproveitamento e encapsulamento de código.

Através desse post, eu espero ter mostrado pra vocês uma forma real de otimização de consultas SQL, sem precisar alterar 1 linha de código, sem precisar alterar nada na sua instância. Em muitos casos, os índices estão sendo utilizados da forma esperada, as estatísticas estão atualizadas, nenhum warning no plano de execução, mas ao utilizar uma função T-SQL, a performance da query acaba sendo ruim.

Neste caso, o uso de funções CLR podem representar uma grande melhoria na sua aplicação/rotina e ganhar preciosos segundos/ciclos de CPU no dia a dia. Com isso, foi possível reduzir várias e várias horas de processamento/CPU diariamente nas instâncias do meu trabalho, simplesmente substituindo funções T-SQL por funções CLR e isso pode ser um grande diferencial na vida do DBA.

Espero que vocês tenham gostado desse post.
Qualquer dúvidas ou críticas, deixem aqui nos comentários.

Abraço!

wagmelo1 disse:

24 de março de 2017 às 10:36

Oi Dirceu, muito bom o seu post!
Principalmente pelo fato de esclarecer que em geral a funções CLR terão melhor desempenho, mas que existem exceções. E que não é uma boa ideia reescrever as funções nativas do SQL.

Responder
- Dirceu Resende disse:
  
  9 de abril de 2017 às 12:31
  
  Wagner,
  Obrigado pelo feedback e espero ter tirado suas dúvidas sobre esse tema.
  
  Qualquer dúvida, estou à disposição.
  
  Responder

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function

Função no CLR sempre vai ser melhor?

Comparando a performance na prática

fncPrimeiroDiaMes

fncUltimoDiaMes

fncSplit

fncBase64_Encode

fncBase64_Decode

fncValida_CPF

fncRecupera_Numeros

fncMes

fncConverte_Em_Horas

fncFormata_Documento

Conclusão

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

Função no CLR sempre vai ser melhor?

Comparando a performance na prática

fncPrimeiroDiaMes

fncUltimoDiaMes

fncSplit

fncBase64_Encode

fncBase64_Decode

fncValida_CPF

fncRecupera_Numeros

fncMes

fncConverte_Em_Horas

fncFormata_Documento

Conclusão

You may also like...

Trabalhando com funções criptográficas (MD4, MD5, SHA1, SHA2_256 e SHA2_512) utilizando a função HASHBYTES do SQL Server

SQL Server – Quando você deve utilizar ORDER BY na consulta e quando não deve utilizar de jeito nenhum!

SQL Server – Como identificar locks, blocks e sessões bloqueadoras

2 Responses

Deixe uma respostaCancelar resposta