Você sabe o que é uma Function?

Se você está acompanhando o portal, já conhece muitas funcionalidades bacanas do SQL Server 2017, mas chegamos no momento de ir além! No artigo de hoje conheceremos um recurso muito útil, chamado Functions.

O que é uma Function?

Uma user defined function (função definida pelo usuário) é uma rotina, normalmente escrita com a linguagem T-SQL, que executa uma ação (como um cálculo complexo) e retorna o resultado dessa ação como um valor. O valor de retorno pode ser um valor escalar (único) ou uma tabela.

Existem também as funções internas do SQL Server que você pode usar para manipular dados.

Funções são muito legais, mas precisam ser usadas com muito cuidado porque melhoraram a produtividade do desenvolvedor, mas você também precisa entender os casos em que seu uso em determinado contexto pode acabar afetando negativamente o desempenho da consulta (conversaremos sobre isso nos próximos artigos).

Um conceito importante é o determinismo das funções. As funções determinísticas sempre retornam o mesmo resultado quando recebem um dado conjunto de parâmetros. As funções não determinísticas podem retornar resultados diferentes cada vez que são executadas com um conjunto específico de parâmetros.

Funções internas do SQL

O SQL Server possui um conjunto imenso de funções, por isso, antes de sair criando várias e várias funções, veja se a que você procura já existe. Vou listar aqui as funções que eu considero mais importantes.

CAST e CONVERT

São funções que convertem um valor de um tipo para outro. CAST é SQL ANSI e CONVERT é T-SQL.

Exemplo: Converta a string “01/02/2017” para uma data.

CONVERT(DATE, ‘01/02/2017’) OU CAST(‘01/02/2017’ AS DATE)

Dica de ouro: se tentar fazer uma conversão sem sentido, por exemplo, converter a palavra DB4B para data, sua query vai falhar. Existem as funções TRY_CAST e TRY_CONVERT, que quando são usadas não geram um erro se a conversão falhar, retornam um valor nulo.

GETDATE()

GETDATE() é uma função não determinística que retorna a data e hora da instância do SQL Server na qual você está conectado. De forma resumida, data e hora corrente com o tipo datetime.

DATEPART

Dada uma data, com esta função extraímos parte da data. Por exemplo: quero extrair da data o mês, ou o ano, ou o dia. Esta função recebe como parâmetro a parte que será extraída (DAY, MONTH, YEAR) e uma data.

Exemplo: DATEPART(month, ‘20170212’) retornará o valor 2.

DATEADD

Não tente fazer somas com datas diretamente. Coisas do tipo ‘20170212’ + 1 , estão totalmente erradas!

Use a função DATEADD, passando como parâmetros o que você vai somar: YEAR, ou MONTH, ou DAY, o número que você vai somar e a data na qual você quer fazer a soma.

Por exemplo, adicione um ano à data ‘20170212’: DATEADD(year, 1, ‘20170212’)

DATEDIFF

Retorna a diferença em dias, anos, meses entre duas datas.

Por exemplo: qual a diferença em dias entre as datas ‘20160212’ e ‘20170212’?

DATEDIFF(day, ‘20160212’, ‘20170212’)

Substring

Imagine que você precisa somente de uma parte de uma string. Para extrair parte de uma string, use a função SUBSTRING passando como parâmetro, a string, a posição inicial e o tamanho da string.

Exemplo: SUBSTRING(‘abcde’, 1, 3) retorna ‘abc’.

LEN

Use a função LEN para saber o tamanho de uma string.

Exemplo: LEN( ‘abc’) retorna o valor 3.

ISNULL

Esta é uma função coringa! Imagine que você quer fazer a soma de dois números, mas um deles é NULL. O retorno dessa soma será NULL, e provavelmente este não é o valor esperado.

Use a função ISNULL para substituir NULL por um valor.

Exemplo: a coluna ABC pode ser NULL, e neste caso você quer retornar o valor 0. Use a função ISNULL(ABC, 0).

Funções de agregação

Essas funções agregam um conjunto de valores e retornam um resultado. São usadas com a cláusula GROUP BY.

Existem várias. As mais usadas são as listadas abaixo:

COUNT: faz uma contagem
AVG: retorna o valor médio de uma coluna
MAX: retorna o maior valor de uma coluna
MIN: retorna o menor valor de uma coluna
SUM: retorna a soma de uma coluna

Todas recebem como parâmetro o nome da coluna. Cabe ressaltar que a função COUNT pode receber como parâmetro o “*”, que indica a contagem de todas as linhas.

Como criar uma função

E se for necessário criar uma função?

Você pode criar funções específicas para o seu contexto de negócio usando o T-SQL.

Função escalar

Lembrando que uma uma função escalar retorna um único valor e que pode receber uma lista de parâmetros.

CREATE FUNCTION schema.Nome da Função  
( @NomeParâmetro AS Tipo Parâmetro) 
RETURNS Data Type do retorno da Função 

AS
    BEGIN  
        Comandos    
        RETURN Valor que a função deve retornar 
    END
Para você ver o uso de uma função de forma mais prática, vamos criar uma função que subtrai dois valores:

CREATE FUNCTION dbo.SubtracaoPostBlog  
( @Valor1 AS INT,
  @valor2 as INT) 
RETURNS INT 
AS
    BEGIN  
        DECLARE @Retorno AS INT
        Set @Retorno = @Valor1 - @Valor2
        RETURN @Retorno 
    END
Para você validar a sua função, faça um SELECT com valores conhecidos e verifique o retorno

SELECT [dbo].[SubtracaoPostBlog] ( 20,10)
GO

Table Valued Function

Esse tipo de função retorna uma tabela. Imagine que dado um parâmetro, você vai executar uma consulta. Esse tipo de function que possui um único comando e o retorno é o resultado da execução deste comando, é chamado de Inline Table-Valued Function.

Se a sua function tem vários comandos e retorna uma tabela, ela é chamada de Multi-Statement Table-Valued Function.

Inline Table-Valued Function

CREATE FUNCTION schema.Nome da Função  
( @NomeParâmetro AS Tipo Parâmetro) 

RETURNS Data Type do retorno da Função 

AS
    BEGIN  
        RETURN Comando SELECT   
    END

Veja o exemplo abaixo, onde a função retornará o valor total de vendas de cada produto de uma loja (vamos usar nosso banco de dados de testes).

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) 
RETURNS TABLE 
AS 
RETURN  
( 
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' 
    FROM Production.Product AS P  
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID 
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID 
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID 
    WHERE C.StoreID = @storeid 
    GROUP BY P.ProductID, P.Name 
); 
GO

Concorda comigo que o retorno deste tipo de função é uma tabela e uma tabela deve ser usada na cláusula FROM? Sendo assim, para testar esse tipo de função você pode executar um comando SELECT usando a function na cláusula FROM.

Select * from Sales.ufn_SalesByStore(934)

Multi-Statement Table-Valued Function

CREATE FUNCTION schema.Nome da Função  
( @NomeParâmetro AS Tipo Parâmetro) 
RETURNS @Variável que armazena a tabela AS Definição da Tabela 

AS
    BEGIN  
        Comandos
        RETURN @Variável que armazena a tabela
    END

Vamos testar a função:

Select * from Sales.ufn_SalesByStore_V2(934)

Conclusão

As functions são um recurso maravilhoso, mas use-as com cuidado porque elas podem impactar o desempenho das consultas (vamos conversar sobre isso nos próximos artigos), e crie somente functions que não existem no SQL Server.

Faça testes! Use a nossa VM, crie consultas, busque funções, crie as suas. Não deixe para testar o que você está aprendendo no seu trabalho.

Esta semana marcamos a nossa entrada na parte mais avançada do SQL Server 2017. Nas próximas semanas criaremos Stored Procedures e Triggers, e você verá que além de programar na sua linguagem favorita, você programa também usando o T-SQL.

Parabéns! A cada semana você está mais perto de ser um desenvolvedor diferenciado!

De 0 a 10, o quanto você recomendaria este artigo para um amigo?

Powered by: