Funções valorizadas em tabela no SQL Server

Funções valorizadas em tabela no SQL Server são óptimas para escrever código SQL DRY, encapsulando trechos de lógica de base de dados comummente utilizados. Contudo, em alguns casos, podem estar na origem de graves problemas de desempenho. Vejamos um exemplo disto. Digamos que temos uma tabela que se parece com a seguinte.

CREATE TABLE Products(ProductId INT,VendorId INT,Description NVARCHAR(1000))

Agora imagine que, no contexto de um procedimento armazenado, temos a seguinte lógica recorrente.

SELECT *FROM ProductsWHERE Description LIKE @Description

Podemos querer refactor isto numa função para tornar o nosso procedimento armazenado mais legível e mais fácil de refactor. Muitos tutoriais na Internet dir-lhe-ão para criar uma função de valor de tabela como se segue.

CREATE FUNCTION FN_Products_GetByDescription (@description NVARCHAR(1000))RETURNS @results TABLE(ProductId INT,VendorId INT,Description NVARCHAR(1000))ASBEGIN;INSERT INTO @resultsSELECT *FROM ProductsWHERE Description LIKE @description;RETURN;END;

O problema com este tipo de função é que actua como uma caixa negra para o código que a chama. Imagine que chamamos a nossa nova função no seguinte cenário.

SELECT *FROM FN_Products_GetByDescription('%book%')WHERE VendorId = 3;

Para executar isto, o SQL Server tem de digitalizar todos os produtos da nossa base de dados, verificando a descrição do produto em relação ao nosso texto de consulta, antes de retornar esse resultado definido para o código de chamada, que depois é filtrado pelo VendorId. Em grandes bases de dados, isto é claramente muito ineficiente. Não seria melhor deixar o SQL Server saber desde o início que queremos filtrar no nosso texto de consulta, bem como o VendorId? Dessa forma, poderia primeiro filtrar por VendorId, e depois executar a dispendiosa filtragem por LIKE num conjunto de resultados mais pequeno. É aqui que a função inlining entra em jogo. Para inlinear a nossa função, simplesmente reescrevemo-la da seguinte forma.

CREATE FUNCTION FN_Products_GetByDescription (@description NVARCHAR(1000))RETURNS TABLEASRETURN(SELECT *FROM ProductsWHERE Description LIKE @description);

Agora, quando o SQL Server executa a consulta acima referida, a função é tratada nos bastidores como parte do código de chamada, em vez de uma caixa negra que precisa de devolver um valor antes de ir mais longe. Isto permite que o optimizador de consultas seja muito mais eficaz na optimização de consultas, porque sabe desde o início exactamente quais os dados de que precisamos.

Então, quando é que alguma vez não quereríamos inlinear funções valorizadas na tabela? Bem, as funções só podem ser alinhadas quando consistem numa única declaração SELECT que retorna um conjunto de resultados. As funções que contêm múltiplas subconsultas que preenchem uma tabela de resultados, bem como as que contêm declarações adicionais tais como FIs não são inlineáveis. No entanto, se conseguir tornar uma consulta inlineable, directamente ou reescrevendo-a ligeiramente, então é geralmente uma ideia muito boa. Os ganhos de desempenho que pode obter com a inlineação de funções podem ser dramáticos, e proporcionar-lhe a flexibilidade e legibilidade do código DRY, sem um grande custo de desempenho.