Table-Valued Parameters: A Clear SQL Server Batch Interface

Sending one database call per order line wastes round trips and makes failure handling awkward. Sending a comma-separated string moves the complexity into parsing and escaping. A table-valued parameter, or TVP, lets an application send a typed set of rows to a procedure in one call.

The important design is the interface contract. Define whether the input is a set of unique products, a sequence of lines that can repeat products, or a collection of independent operations. Those choices determine keys, validation, correlation, and what a retry means.

Define a type that expresses the input

The practice interface accepts one positive quantity per product. It reports whether each product exists instead of silently discarding unknown identifiers.

-- 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 primary key rejects duplicate ProductId values in this contract. If repeated products are legitimate separate lines, use a LineId key and retain ProductId as an attribute instead. Do not deduplicate real order lines merely because a set-based interface is convenient.

The positive quantity check rejects zero and negative values, while NOT NULL prevents an unknown quantity from passing through three-valued logic. Type constraints are useful for shape and simple invariants. They do not replace checks against current business data, such as product status or availability.

READONLY is required for a TVP parameter. The procedure can query and join the supplied rows but cannot update that parameter as if it were a mutable temporary table. If transformations are necessary, create a separate working table and make that extra cost explicit.

The sample call includes one unknown product.

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

Products 1 and 2 are valid and product 99 is flagged. The left join preserves the invalid input so it can be reported. An inner join would hide it and could make a partial result look like successful validation of the whole request.

Keep validation and execution consistent

This procedure is a validation demonstration, not an order-placement transaction. If the next step writes an order, decide whether one invalid line rejects the entire request or whether partial acceptance is allowed. Return a result correlated to each input line, and make the overall status unambiguous.

A successful validation call does not freeze product state for a later write. Prices, availability, and permissions can change between calls. Recheck necessary conditions inside the actual transaction, or use an appropriate version contract. Do not treat a preflight report as a concurrency guarantee.

TVPs have no inherent row order. Include an explicit sequence column when ordering matters and use ORDER BY on results. For generated destination identifiers, return the source LineId alongside the resulting key rather than relying on the order in which rows happen to be returned.

In a .NET client, bind a structured parameter with the correct schema-qualified type name and compatible column types. DataTable or a streamed row sequence can represent the input. Match precision, scale, lengths, and nullability deliberately; a parameter named correctly can still carry the wrong shape.

Measure the cost at realistic batch sizes

SQL Server does not maintain column statistics on TVPs. A procedure that handles five rows and another execution that handles fifty thousand can therefore need different plans. A primary key gives uniqueness information and an access structure, but it does not supply a distribution histogram.

For larger or highly variable inputs, copying into a temporary table with suitable indexes and statistics may help the subsequent joins. That introduces copying and tempdb work, so measure the complete call rather than timing only the final SELECT. Recompilation may help some cardinality-sensitive cases but does not create missing distribution statistics.

Set a practical maximum request size and consider bounded batches for very large imports. TVPs are not automatically faster than bulk loading at every size. Include serialization time, network transfer, compilation, and execution in the comparison.

The caller also needs the appropriate procedure and type permissions, including REFERENCES where required. Keep deployment rights separate from runtime use of the type. Changing a user-defined table type's shape generally requires a versioned deployment of dependent objects; plan compatibility with older clients.

Test empty input, duplicates, bad quantities, unknown products, maximum batch size, and a retry after an uncertain response. A useful TVP interface removes round-trip overhead while preserving precise per-row and whole-request behavior.

Technical references: Microsoft Learn: Table-valued parameters · Microsoft Learn: CREATE TYPE.