Qual é a diferença entre funções com valor de tabela inline e de múltiplas instruções no sql?

Primeiro, acredito que esta questão seja específica do MS SQL Server. Eu sei que a Oracle tem algum suporte de função com valor de tabela, mas é totalmente diferente, e eu nunca fui capaz de entender isso nos seis anos em que usei a Oracle.

A resposta de Sachin é verdadeira, mas perde o impacto no desempenho, que é o maior motivo para escolher uma função com valor de tabela em linha (ITVF). Um ITVF é realmente apenas uma visualização com parâmetros. É uma expressão, não uma série de etapas que devem ser executadas; portanto, o SQL Server a combina com a consulta que a utiliza e avalia a coisa toda de uma só vez. Se o seu ITVF incluir 200 pesquisas, mas a consulta de chamada desejar apenas uma delas, se você tiver cuidado com a forma como escreveu o ITVF, o otimizador poderá ignorar completamente todas as pesquisas que não foram solicitadas. Isso é realmente bom para tornar o código reutilizável em uma ampla variedade de lugares.

Itens a serem lembrados para o desempenho ao escrever um ITVF:

  • Não passe as chaves como parâmetros. Você acaba com um código bonito e fácil de seguir, mas ele força tudo a entrar em loop e o desempenho é terrível. Salve os parâmetros para períodos efetivos e junte-se ao ITVF para limitar sua população.
  • Tente escrever o ITVF em torno de uma tabela principal, com pesquisas baseadas nela. As pesquisas devem ser "aplicação externa (selecione a parte superior 1)" ou as associações à esquerda em uma coluna de chave primária. Nesses casos, a pesquisa não pode aumentar nem diminuir o número de linhas retornadas, e o otimizador pode provar isso, para que o otimizador possa eliminá-las se não tiverem sido usadas. Isso vale para visualizações também.

Tipos de funções definidas pelo usuário:

  • Função escalar
  • Função com valor de tabela
  • Função com valor de tabela embutida
  • Função avaliada da tabela de múltiplas instruções
  • FUNÇÃO ESCALAR

Funções escalares retornam apenas valor escalar / único. Podemos usar a função escalar na instrução SELECT, WHERE, GROUP BY, HAVING. Temos que mencionar o tipo de dados desse valor único na cláusula RETURNS da definição da função.

Exemplo: Definição de Função -

  1. CREATE FUNCTION GetDiseasedPatientsNumber (@DiseaseCode INT) RETORNA INTASBEGIN DECLARAR @Number_of_Patients INT SELECT @Number_of_Patients = COUNT (PatientID) FROM [dbo]. [Pacientes] WHERE DiseaseCode = @DiseaseCode RETURN @Number_of_Página

Uso da função escalar acima -

  1. - Use na instrução SelectSELECT ProviderID, [Nome do Provedor], [Código da Clínica], [dbo]. [GetDiseasedPatientsNumber] (DCode) AS 'Pacientes com Diabetes Contam'FROM [dbo]. [Provider] - Use na cláusula WhereSELECT ProviderID, [Nome do provedor], [Código da clínica] DE [dbo]. [Provedor] ONDE [dbo]. [GetDiseasedPatientsNumber] (DiseaseCode)> = 150
  • TABELA DE VALOR FUNÇÃO

Uma função definida pelo usuário com valor de tabela retorna um conjunto de resultados / conjunto de linhas em vez de um valor único / escalar. Pode ser chamado na cláusula FROM ou JOIN de uma consulta SELECT. A função com valor de tabela pode ser uma boa alternativa para uma VIEW, pois VIEW não permite parâmetros, enquanto as funções com valor de tabela permitem parâmetros. Às vezes eles são chamados como

'Visualizações parametrizadas'.

  • MESA EM LINHA VALORIZADA DA FUNÇÃO

Uma definição de função com valor de tabela embutida especifica apenas RETURNS TABLE e não a definição da tabela. Todo o lote ou código de consulta ou consulta de seleção dentro da função é uma única instrução RETURN.

Exemplo: Definição de Função -

  1. CREATE FUNCTION CustomerProductDetails (@CustomerID NCHAR (5)) RETURNS TABLEASRETURN (SELECT C.CustomerID, C.CompanyName, C.City, O.OrderID, O.OrderDate, P.ProductName, OD.UnitPrice, OD.Quantity, OD.Discount FROM [Produtos] P INNER JOIN [Detalhes do pedido] OD ON P.ProductID = OD.ProductID INNER JOIN Pedidos O ON O.OrderID = OD.OrderID INNER JOIN Clientes C ON C.CustomerID = O.CustomerID WHERE C.CustomerID = @ CustomerID) GO

Uso da função acima na cláusula FROM -

  1. - Simple Select statementSELECT * FROM dbo.CustomerProductDetails ('VINET') - JOIN com Employee TABLESELECT E.EmployeeID, E.LastName, E.FirstName, E.Title, CP.CustomerID, CP.CompanyName, CP.City, CP .OrderID, CP.OrderDate, CP.EmployeeID, CP.ProductName, CP.UnitPrice, CP.Quantity, CP.DiscountFROM [funcionários] EJOIN dbo.CustomerProductDetails ('VINET') CP ON CP.EmployeeID = E.EmployeeID
  • FUNÇÃO VALORIZADA DA TABELA MULTI DE DECLARAÇÃO

A definição de função com valor de tabela de múltiplas instruções especifica RETURNS junto com a definição de TABLE VARIABLE. O corpo da função pode consistir em várias instruções e uma das quais preencherá essa TABELA VARIABLE. E o escopo / vida útil desta TABELA VARIABLE é limitado apenas a esta função - fora desta função, ela não está disponível.

Exemplo: Definição de Função -

  1. CREATE FUNCTION CustomerOrderDetails (@CustomerID NCHAR (5)) DEVOLVE @CustomerOrders TABELA (CustomerID NCHAR (5), CompanyName NVARCHAR (40), OrderID INT, OrderDate DATETIME) ASBEGIN INSERT EM @CustomerOrders SELECT C.CustomerID, C.Company OrderID, O.OrderDate DOS clientes C INNER JOIN Pedidos O ON C.CustomerID = C.CustomerID WHERE C.CustomerID = @CustomerID IF @@ ROWCOUNT = 0 INICIAR INSERÇÃO EM VALORES @CustomerOrders ('', 'Nenhum pedido encontrado', 0 , GETDATE ()) END RETURNENDGO

Diferença entre as funções avaliadas da tabela em linha e de múltiplas instruções:

Nota:

  • Quando precisamos executar uma manipulação extra de dados em dados SELECT como INSERT, UPDATE, DELETE ou qualquer filtragem - antes de inserir a saída final na variável de tabela -, optamos pela função com valor de tabela MULTI-STATEMENT.
  • Quando precisamos executar alguma lógica complexa no parâmetro Input antes de começar a usá-lo na cláusula WHERE - vamos para a função com valor de tabela MULTI-STATEMENT.
  • Quando queremos implementar uma lógica do tipo VIEW, mas também queremos passar um parâmetro - Parameterized View -, optamos pela função de valor da tabela embutida.

Limitações nas funções definidas pelo usuário do SQL:

  • As funções de construção não determinística não podem ser usadas em funções definidas pelo usuário. por exemplo, GETDATE () ou RAND ().
  • O tipo de dados XML não é suportado.
  • Consultas dinâmicas de SQL não são permitidas.
  • As funções definidas pelo usuário não suportam nenhuma instrução DML (INSERT, UPDATE, DELETE), a menos que seja executada na variável de tabela.
  • Não podemos fazer uma chamada para o procedimento armazenado.
  • Não podemos criar tabelas temporárias dentro de UDFs.
  • Ele não suporta o tratamento de erros no UDF. Embora, possamos manipular erros (RAISEERROR, TRY-CATCH) para as instruções que usam essa função.

Por hoje é isso. Espero que isso seja útil. :)

Boa sorte. :)