Parâmetros de tabela como interface em lote no SQL Server

Uma chamada ao banco por linha de pedido desperdiça viagens de rede e dificulta falhas. Uma string separada por vírgulas transfere a complexidade para análise e escape. Um parâmetro de tabela, ou TVP, permite enviar um conjunto tipado para um procedimento em uma chamada.

Defina o contrato primeiro: produtos únicos, linhas com produtos repetidos ou operações independentes. Essa escolha determina chaves, validação, correlação e significado de repetição.

Descrever a entrada com um tipo

A interface de prática aceita quantidade positiva por produto e informa produtos desconhecidos.

-- Disposable practice database. Send GO-separated batches separately.
CREATE TYPE dbo.RequestLinesDemo AS TABLE (
    ProductId int NOT NULL PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity > 0)
);
GO
CREATE TABLE dbo.ProductsTvpDemo(ProductId int PRIMARY KEY, UnitPrice decimal(12,2));
INSERT dbo.ProductsTvpDemo VALUES (1,10.00),(2,15.00);
GO
CREATE PROCEDURE dbo.ValidateLinesDemo @Lines dbo.RequestLinesDemo READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT l.ProductId, l.Quantity, p.UnitPrice,
           CONVERT(bit, CASE WHEN p.ProductId IS NULL THEN 0 ELSE 1 END) AS IsValid
    FROM @Lines AS l
    LEFT JOIN dbo.ProductsTvpDemo AS p ON p.ProductId = l.ProductId
    ORDER BY l.ProductId;
END;
GO

A chave primária rejeita ProductId duplicado. Se produtos repetidos representam linhas legítimas, use LineId como chave e mantenha ProductId como atributo. Não elimine posições reais somente para facilitar uma interface orientada a conjuntos.

CHECK exige quantidade positiva e NOT NULL impede que um valor desconhecido passe. Restrições do tipo validam formato e invariantes simples, mas não substituem verificações contra catálogo, disponibilidade ou status atuais.

READONLY é obrigatório no parâmetro TVP. O procedimento pode consultar e juntar as linhas, mas não modificar o parâmetro como uma tabela temporária. Transformações exigem outra estrutura de trabalho e seu custo deve ser explícito.

A chamada inclui um produto inexistente.

DECLARE @Input dbo.RequestLinesDemo;
INSERT @Input VALUES (1,2),(2,3),(99,1);
EXEC dbo.ValidateLinesDemo @Lines = @Input;

Produtos 1 e 2 são válidos, enquanto 99 é marcado. LEFT JOIN preserva a entrada inválida para relatá-la. INNER JOIN a esconderia e poderia fazer uma resposta parcial parecer validação completa.

Preservar o contrato da operação

O procedimento demonstra validação, não uma transação de pedido concluída. Decida se uma linha inválida rejeita tudo ou se aceitação parcial é permitida. Relacione cada resultado à sua entrada e mantenha o estado global inequívoco.

Validação bem-sucedida não congela os produtos para a próxima gravação. Preço, disponibilidade e permissões podem mudar entre chamadas. Confira condições necessárias dentro da transação real ou adote um contrato de versão adequado.

TVPs não possuem ordem inerente. Acrescente sequência explícita quando necessária e ORDER BY na saída. Para identificadores gerados, devolva LineId da origem junto da chave de destino em vez de depender da ordem de retorno.

Em .NET, configure parâmetro estruturado com o nome correto do tipo qualificado por esquema. DataTable ou um fluxo de linhas pode representar a entrada. Alinhe tipos, precisão, escala, comprimento e nulabilidade. Nome correto não corrige colunas incompatíveis.

Medir os tamanhos reais

O SQL Server não mantém estatísticas de colunas de TVPs. Cinco linhas e cinquenta mil podem exigir planos diferentes. Uma chave primária oferece unicidade e estrutura de acesso, mas não um histograma de distribuição.

Para entradas grandes e variáveis, copiar para tabela temporária com índices e estatísticas pode melhorar junções posteriores. Isso adiciona cópia e trabalho no tempdb. Meça a chamada inteira, não somente o SELECT final. Recompilação pode ajudar alguns casos de cardinalidade sem criar as estatísticas ausentes.

Estabeleça tamanho máximo e considere lotes limitados para importações muito grandes. TVP não é automaticamente mais rápido que bulk load em qualquer tamanho. Inclua serialização, rede, compilação e execução na comparação.

O chamador precisa de permissões de procedimento e tipo, incluindo REFERENCES quando exigido. Separe direitos de implantação do uso em produção. Alterar um tipo de tabela normalmente requer implantação versionada de objetos dependentes e estratégia para clientes antigos.

Teste vazio, duplicatas, quantidades inválidas, produtos desconhecidos, tamanho máximo e repetição após resposta incerta. Uma interface útil reduz viagens de rede sem perder o significado de cada linha ou da solicitação inteira.

Referências técnicas: Microsoft Learn: Table-valued parameters · Microsoft Learn: CREATE TYPE.