Use TVF (Table-Valued Function) !

Posted on junho 9, 2011

0


A TVF é um novo recurso do SQL Server 2008, ela é um tipo de função na qual o valor retornado seja um tipo tabela, tipo tabela? Isso mesmo, geralmente o retorno de uma função é um valor escalar, porém nessa função, visualizamos uma tabela.

Podemos dizer que a Table-Valued Function seria uma view na qual podemos passar parametros ou seja, seria  uma espécia de view parametrizada. Para maiores informações sobre view’s veja: https://luanmorenodba.wordpress.com/2011/06/03/opes-de-criao-de-uma-view-e-entendimento-de-erros-gerados/ 

Para entendermos melhor a TVF, vamos realizar o seguinte  script:

 

USE tempdb

go

 

 

IF OBJECT_ID(‘EmpresaConsultoria’,‘U’) IS NOT NULL

      DROP TABLE EmpresaConsultoria

 

IF OBJECT_ID(‘Clientes’,‘U’) IS NOT NULL

      DROP TABLE Clientes

 

–Criação da Tabela

CREATE TABLE Clientes

(

      ID INT IDENTITY(1,1) NOT NULL

  , Nome VARCHAR(20) NOT NULL

  , CPF VARCHAR(14) NOT NULL

  , DataNascimento DATE NOT NULL

  , Sexo CHAR(12) NOT NULL

  , EstadoCivil VARCHAR(20) NOT NULL

  , Profissao VARCHAR(50) NULL

  , UF CHAR(2) NULL

)

GO

 

–Criação de Constraints

ALTER TABLE Clientes

ADD CONSTRAINT PK_Clientes_ID

PRIMARY KEY (ID)

GO

 

ALTER TABLE Clientes

ADD CONSTRAINT UNQ_Clientes_Nome_CPF_DataNascimento

UNIQUE (Nome, CPF, DataNascimento)

GO

 

CREATE NONCLUSTERED INDEX idxNCL_Clientes_UF

ON Clientes(UF)

 

 

–Criação da Tabela

CREATE TABLE EmpresaConsultoria

(

      ID SMALLINT IDENTITY(1,1) NOT NULL

  , IDPessoaFisica INT NOT NULL

  ,   Nome VARCHAR(30) NOT NULL

  ,   CNPJ VARCHAR(20) NULL

)

 

–Criação de Constraints

ALTER TABLE EmpresaConsultoria

ADD CONSTRAINT PK_EmpresaConsultoria_ID

PRIMARY KEY (ID)

GO

 

ALTER TABLE EmpresaConsultoria

ADD CONSTRAINT FK_EmpresaConsultoria_Cliente_IDPessoaFisica

FOREIGN KEY (IDPessoaFisica)

REFERENCES Clientes(ID)

 

 

–Inserção de Dados Básicos

INSERT INTO Clientes(Nome, CPF, DataNascimento, Sexo, EstadoCivil, Profissao, UF)

VALUES (‘Luan.Moreno’, ‘022.366.551-77’, ‘1988-07-20’, ‘Masculino’, ‘Solteiro’, ‘DBA’, ‘DF’),

         (‘GB’, ‘551.223.542-12’, ‘1980-12-12’, ‘Masculino’, ‘Casado’, ‘Desenvolvedor’, ‘SP’),

         (‘Luciano.Caixeta’, ‘222.341.231-12’, ‘1982-03-12’, ‘Masculino’, ‘Casado’, ‘Consultor’, ‘DF’),

         (‘Vitor.Meriat’, ‘123.332.511-17’, ‘1990-01-20’, ‘Masculino’, ‘Casado’, ‘Desenvolvedor’, ‘MG’)

 

INSERT INTO EmpresaConsultoria(IDPessoaFisica, Nome, CNPJ)

VALUES (1,‘ConsultoriaBDNR’, ‘033.455.77.655’),

         (1,‘BIDSNDS’, ‘033.455.77.655’)

 

SELECT  *

FROM Clientes

 

SELECT  *

FROM EmpresaConsultoria

Podemos ver os seguintes dados inseridos nas tabelas.

 

image

 

 

 

image

Agora vamos realizar a criação da TVF, que fará uma busca de registros na tabela de clientes tendo como parâmetro de entrada o IDPessoaFisica, e logo depois vamos consultar a empresa na qual esta pessoa está vinculada.

–Criação da TVF

IF OBJECT_ID(‘fn_RecuperaClientes’) IS NOT NULL

      DROP FUNCTION fn_RecuperaClientes

GO

 

CREATE FUNCTION fn_RecuperaClientes

      (@IDPessoaFisica VARCHAR(20)) RETURNS TABLE

AS

RETURN

SELECT  *

FROM tempdb.dbo.Clientes AS C

WHERE C.ID = @IDPessoaFisica

Executando a função passando o IDPessoaFisica como parâmetro temos o seguinte resultado:

SELECT  *

FROM tempdb.dbo.fn_RecuperaClientes(1)IDPessoaFisica

image

Como o índice cluster da tabela de Clientes está criada em cima de ID, e na função que acabamos de criar estamos buscando o registro pelo ID, temos o melhor plano possível, que será um Index SeeK em cima do Índice Cluster.

image

Agora faremos uma busca na tabela de EmpresaConsultoria buscando a informação da empresa no qual os clientes estão cadastrados. Para conseguirmos realizar essa busca vamos fazer um CROSS APPLYcom a tabela, veja no script.

SELECT  *

FROM tempdb.dbo.EmpresaConsultoria AS EC

CROSS APPLY tempdb.dbo.fn_RecuperaClientes(EC.IDPessoaFisica)

Temos o seguinte resultado:

image

Obs: Para realizar JOINS com outras tabelas utilizando TVF use o CROSS APPLY.

Aonde usar TVF:

  1. Consultas na qual utilize somente SELECT como código, porque a TVF é limitada para isso.
  2. Use quando for necessário que seja criado uma visualização na qual a busca de dados seja com parâmetros.