Introdução às funções do SQL Server, seus benefícios e desvantagens
O que é uma função SQL?
As funções do SQL Server podem ser usadas para retornar valores únicos (escalador) ou tabelas, usando rotinas T-SQL ou CLR (tempo de execução de linguagem comum) e geralmente realizando cálculos mais complexos do que você gostaria de usar no código geral.
Quando é uma boa ideia usar uma função em vez de código embutido?
Bom uso
As funções podem ser usadas para substituir visualizações (retornar uma tabela), como uma coluna calculada em uma tabela, realizar ações de pesquisa consistentes ou simplesmente para modularizar seu código, o que pode ajudar a reduzir as alterações necessárias.
Mau uso
Vemos isso o tempo todo, mas as funções não devem ser usadas para retornar dados de pesquisa no lugar de uma junção quando você está lidando com grandes conjuntos de dados. Cada linha chamará a mesma função, mesmo que já tenha encontrado esse valor. Nesses casos, use uma junção.
Exemplos de função de escalonador
As funções do scaler são melhor usadas para executar lógica, como reformatação ou cálculos baseados em linha, pois, por sua natureza, são chamadas para cada linha, podem ser usadas para pesquisar dados em outra tabela, mas, em geral, você obterá melhor desempenho usando uma junção. Para isso, podemos ver nossa função get age no link a seguir.
Armazenar a idade de alguém no momento em que preencheu um formulário não faria sentido, pois quando os dados forem consultados posteriormente, estarão desatualizados. Uma opção melhor seria capturar uma data de nascimento e calculá-la na hora. Em nossa função adicionamos um campo até, que pode ser usado para retroceder um cálculo, ou talvez de forma mais sombria, calcular a idade por hora da morte (esta função foi estendida para um contrato do NHS).
Example
CREATE FUNCTION [Dates].[GetAge](@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDExemplos de função de escalonador
Para usar isso de uma tabela fictícia, simplesmente usaríamos this, que forneceria a idade atual ou a idade da morte.
Use in a select statement
SELECT [PersonID],[DateOfBirth],[dbo].[GetAge]([DateOfBirth],[DeceasedDate]) AgeAsFunction,--Simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]Exemplos de função de escalonador
Vantagens : Consistente, modular, mais compacto, reduz potencialmente o número de mudanças
Desvantagens : Para ver o código você precisa olhar na função
Embora seja geralmente útil, essa função também é extremamente precisa, porque utiliza uma função de ano bissexto. É não determinístico por natureza, portanto, nunca deve ser armazenado como dados persistentes.
Exemplos de colunas da tabela
As colunas calculadas podem ser adicionadas como persistentes (alteradas quando os dados o fazem) ou não persistentes (calculadas sempre que a linha é selecionada). Podemos ver duas maneiras de usá-los aqui em nosso Sistema de Gerenciamento de Conteúdo.
Nota : Dados persistentes podem ser mais difíceis de obter, pois exigem que um conjunto de restrições seja atendido
Não persistente: Idade
Usando a função age como acima, podemos adicionar isso em uma tabela e passar valores de outras colunas. Em seguida, basta selecioná-lo como uma coluna.
Add to a table
CREATE TABLE [Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,[DateOfBirth] [datetime] NULL,[Age] AS ([dbo].[GetAge]([DateOfBirth],[DeceasedDate])),[DeceasedDate] [datetime] NULL)Select Statement
SELECT [PersonID],[DateOfBirth],[Age] AgeAsColumn,--Even simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]Não persistente: Idade
Vantagens : Consistente, modular
Desvantagens : Retarda a velocidade de consulta se não for necessário.
Persistente: CSS minificado
Temos uma função que reduz o espaço necessário para CSS em até 30%. Chamar isso regularmente diminuiria a velocidade de seleção da tabela e, como os dados raramente são atualizados, fazia sentido realizar cálculos no momento da inserção/atualização. Ao criar a coluna como uma função, também não precisamos realizar essas operações como gatilho.
Add to a Table
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)Persistente: CSS minificado
Ele pode ser selecionado como uma coluna normal e os dados são armazenados na tabela. Também evita o uso de uma declaração de substituição massiva que incha nosso código.
Vantagens : Velocidade de seleção consistente, modular, mais rápida, sem necessidade de gatilho!
Desvantagens : Aumenta o espaço necessário para a mesa, diminui a velocidade de inserção
Substituindo uma visualização
Nós tendemos a não usar visualizações, exceto quando usamos regularmente as mesmas junções em vários lugares.
Mesmo nesses casos, não há razão para que uma função de tabela não possa ser usada de forma mais eficaz. A tabela que usamos pode ser encontrada no link abaixo, e temos dois exemplos de uso, um via função e outro via view.
Create a function
CREATE FUNCTION Dates.GetCalender(@DateFrom DATETIME2,@DateTo DATETIME2,@Function INT) RETURNS @D TABLE (CalenderDate DATETIME2(7) NOT NULL PRIMARY KEY,CalenderCA INT NULL,CalenderCD INT NULL,WeekDayID INT NULL,WeekDayName VARCHAR(9) NULL,HolidayType NVARCHAR(100)) AS BEGININSERT INTO @DSELECT c.*,HolidayTypeFROM [Utilities].[Dates].[Calender] cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND CalenderFunction=@FunctionWHERE c.CalenderDate BETWEEN @DateFrom AND @DateTo RETURNENDGOCreate a view
CREATE VIEW Dates.GetCalenderView ASSELECT c.CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName,h.HolidayType,c.CalenderFunctionFROM (SELECT CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName, CalenderFunction FROM [Utilities].[Dates].[Calender],(SELECT DISTINCT CalenderFunction FROM Dates.CalenderHolidays) x) cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND h.CalenderFunction=c.CalenderFunctionUsage
SELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',0) --England & WalesSELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',1) --Scotland----OR----SELECT * FROM Dates.GetCalenderView WHERE CalenderDate BETWEEN '2018-01-01' AND '2018-12-31' AND CalenderFunction=0Substituindo uma visualização
Benefícios : Compacto para chamar, retornado com Chave Primária (perfeito para mais junção), os parâmetros podem ser usados anteriormente no código.
Desvantagens : Mais código para construir, menos flexível
Usar em junções de aplicação
As funções de tabela são ótimas para usar em Apply Joins, pois os dados podem ser passados linha por linha. Usamos nossa função TextToRows para separar strings no SQL Server. No exemplo abaixo, usamos um double apply para dividir os dados duas vezes com delimitadores diferentes.
SQL Code
DECLARE @TestText TABLE(TextToSplit NVARCHAR(100))INSERT INTO @TestText SELECT 'Text,To,Tows:Split,One'INSERT INTO @TestText SELECT 'Text,To,Tows:Split,Two'SELECT t.TextToSplit,s1.WordInt,s1.WordStr,s2.WordInt,s2.WordStrFROM @TestText tOUTER APPLY dbo.TextToRows(':',TextToSplit) s1OUTER APPLY dbo.TextToRows(',',WordStr) s2Further detail
Some of the functions we have written can be found below.
