Blog – Fabiano Neves Amorim

IF OBJECT_ID(‘VendaPorCliente’) IS NOT NULL

  DROP FUNCTION dbo.VendaPorCliente

GO

CREATE FUNCTION dbo.VendaPorCliente(@CustomerID Int)

RETURNS Decimal(18,2)

AS

BEGIN

  DECLARE @Total Decimal(18,2)

  SELECT @Total = SUM(OrderQty * UnitPrice)

    FROM AdventureWorks.Sales.SalesOrderHeader a

   INNER JOIN AdventureWorks.Sales.SalesOrderDetail b

      ON a.SalesOrderID = b.SalesOrderID

   WHERE a.CustomerID = @CustomerID

  RETURN @Total

END

GO

/*

                Seleciona o total de venda por Customer

   Aqui temos um problema, pois para cada linha na tabela Customer o SQL Server irá

   executar a Function VendaPorCliente, ou seja se minha tabela Customer tiver

   50000 linhas o SQL irá acessar as tabelas de header e Detail 50000 vezes.

*/

SELECT AccountNumber, dbo.VendaPorCliente(CustomerID) as Total

  FROM AdventureWorks.Sales.Customer

/*

   Para resolver o problema da consuta acima poderiamos fazer o seguinte

   Criar uma nova function do tipo "multi-statement table-valued"

*/

IF OBJECT_ID(‘VendaTotalClientes’) IS NOT NULL

  DROP FUNCTION dbo.VendaTotalClientes

GO

CREATE FUNCTION dbo.VendaTotalClientes()

RETURNS @tb_result TABLE

(

  CustomerID Int,

  Total      Decimal(18,2),

  PRIMARY KEY(CustomerID)

)

AS

BEGIN

  INSERT INTO @tb_result 

  SELECT a.CustomerID, SUM(OrderQty * UnitPrice) Total

    FROM AdventureWorks.Sales.SalesOrderHeader a

   INNER JOIN AdventureWorks.Sales.SalesOrderDetail b

      ON a.SalesOrderID = b.SalesOrderID

   GROUP BY a.CustomerID

  RETURN

END

GO

/*

               Seleciona o total de venda por Customer

   Desta vez ao invés de acessar a function para cada linha da tabela Customer

   o SQL Server irá ler os dados das tabelas Header e Detail apenas 1 vez pois a function

   irá retornar todos os dados em uma tabela.

*/

SELECT AccountNumber, b.Total

  FROM AdventureWorks.Sales.Customer a

 INNER JOIN dbo.VendaTotalClientes() b

    ON a.CustomerID = b.CustomerID

GO

/*

   Agora chegamos onde eu queria!, imagine que eu queria retornar o total de venda apenas do

   Customer ‘AW00000001’, eu iriá escrever o seguinte select.

   O que acontece abaixo é que o SQL irá primeiro retornar todos os dados da function, ou seja,

   todas as vendas por customer e depois aplicar o filtro de AccountNumber = ‘AW00000001’

*/

SELECT AccountNumber, b.Total

  FROM AdventureWorks.Sales.Customer a

 INNER JOIN dbo.VendaTotalClientes() b

    ON a.CustomerID = b.CustomerID

 WHERE a.AccountNumber = ‘AW00000001’

GO

/*

  O ideal neste caso seria usar:

*/

SELECT AccountNumber, dbo.VendaPorCliente(CustomerID) as Total

  FROM AdventureWorks.Sales.Customer

 WHERE AccountNumber = ‘AW00000001’

GO

/*

  Ou então simplesmente não utilizar a function e fazer o select direto nas tabelas

*/

SELECT a.AccountNumber, SUM(OrderQty * UnitPrice) AS Total

  FROM AdventureWorks.Sales.Customer a

 INNER JOIN  AdventureWorks.Sales.SalesOrderHeader b

    ON a.CustomerID = b.CustomerID

 INNER JOIN AdventureWorks.Sales.SalesOrderDetail c

    ON b.SalesOrderID = c.SalesOrderID

 WHERE a.AccountNumber = ‘AW00000001’

 GROUP BY a.AccountNumber

GO