Stream the rows.
Skip the DataTable.

When a list becomes a lot of parameters

You have a list of document IDs and want the matching documents from SQL Server. A common approach is to create one parameter for each ID:

SELECT Title FROM dbo.Documents WHERE Id IN (@p0, @p1, @p2, @p3);

That works well for small lists. A query builder or Dapper can generate the placeholders for you, but the database still receives separate parameters. As the list grows, so does the parameter collection, and the SQL text changes with the number of placeholders.

SQL Server has a limit of 2,100 parameters per stored procedure, and expanded parameter lists can hit that limit through the client’s execution path. Other query parameters also consume that budget. More importantly, a set of IDs is naturally tabular data. We can send it that way.

Send the set as one parameter

A table-valued parameter (TVP) carries multiple rows in one typed parameter. It is useful for batch lookups, permission checks or a set of rows to update. Define the row shape once in the database:

CREATE TYPE dbo.DocumentIds AS TABLE (Id nvarchar(256) NOT NULL);

The query can now use a stable parameter name regardless of how many IDs are supplied:

SELECT d.Title FROM dbo.Documents AS d WHERE d.Id IN (SELECT Id FROM @Ids) ORDER BY d.Id;

@Ids is a table inside the query. The IN subquery gives us set-membership behavior: duplicate IDs in the input do not duplicate documents in the output. We still use one database command, just as we did with the expanded parameter list. The difference is how the input is represented.

For a stored procedure, declare the parameter as @Ids dbo.DocumentIds READONLY. The type name and column schema are the contract between the application and SQL Server.

The straightforward implementation: DataTable

These examples use .NET 10 and C# 14, with Microsoft.Data.SqlClient. Assume connection is an open SqlConnection and ids is a populated string[] of valid IDs. The snippets omit validation and connection setup to keep the TVP code visible.

using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;

string[] ids = ["doc-17", "doc-42", "doc-99"];

DataTable is a convenient way to build the input. Add a column, then add the values:

using var table = new DataTable();
table.Columns.Add("Id", typeof(string));
foreach (var id in ids) table.Rows.Add(id);

using var command = new SqlCommand("SELECT d.Title FROM dbo.Documents AS d WHERE d.Id IN (SELECT Id FROM @Ids) ORDER BY d.Id;", connection);
command.Parameters.Add(new SqlParameter("@Ids", SqlDbType.Structured) { TypeName = "dbo.DocumentIds", Value = table });

SqlDbType.Structured identifies the TVP. TypeName selects the database table type, and Value supplies its rows. That is enough to use a TVP; streaming is a separate optimization.

On a busy path, remove the intermediate table

Now consider a service that performs these lookups repeatedly: checking access to a batch of documents, filtering a large result set, or handling many concurrent requests. Each call builds a DataTable only to hand its rows to the driver and discard it.

The table brings row objects, storage and bookkeeping. When that allocation shows up in a profile, we can keep the TVP and replace its client-side representation. SqlClient also accepts IEnumerable<SqlDataRecord>.

public static class DocumentLookup
{
    private static readonly SqlMetaData[] Metadata = [new("Id", SqlDbType.NVarChar, 256)];

    public static IEnumerable<SqlDataRecord> Records(string[] ids)
    {
        var record = new SqlDataRecord(Metadata);
        foreach (var id in ids)
        {
            record.SetString(0, id);
            yield return record;
        }
    }
}

The metadata describes the same nvarchar(256) column as the SQL type. It is cached once. Each enumeration owns one mutable record, updates its first column, and yields it to the driver.

Use that sequence directly as the parameter value:

using var command = new SqlCommand("SELECT d.Title FROM dbo.Documents AS d WHERE d.Id IN (SELECT Id FROM @Ids) ORDER BY d.Id;", connection);
command.Parameters.Add(new SqlParameter("@Ids", SqlDbType.Structured) { TypeName = "dbo.DocumentIds", Value = DocumentLookup.Records(ids) });
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync()) Console.WriteLine(reader.GetString(0));

The SQL query and table type have not changed. We have removed the intermediate DataTable. The array of input strings still exists; this optimization does not remove that input or make the entire database call allocation-free.

Why reusing the record works

Records(ids) is deferred. The driver consumes it when the command executes. After each yield return record, SqlClient reads the current column values before asking the iterator for its next row.

For the next ID, the iterator changes the same record. The previous row’s values have already been consumed by the serialization path. SqlClient’s row-consumption implementation shows this order: fill the current row’s fields, then advance the enumerator.

A row is not necessarily a network packet. SqlClient buffers the serialized data, and SQL Server still receives the rows and performs the query. The saving is in preparing the input on the client.

The record must stay local to the enumeration. Do not make it static, and do not materialize the sequence with ToList() or ToArray(): those collections would hold repeated references to the same mutable object.

The same TVP with Dapper

With using Dapper;, the wrapper fits directly into the parameter object:

var sql = "SELECT d.Title FROM dbo.Documents AS d WHERE d.Id IN (SELECT Id FROM @Ids) ORDER BY d.Id;";
var parameters = new { Ids = DocumentLookup.Records(ids).AsTableValuedParameter("dbo.DocumentIds") };
var titles = await connection.QueryAsync<string>(sql, parameters);

AsTableValuedParameter configures the structured parameter for the provider. Dapper checks whether the sequence contains a row before SqlClient consumes it, so the input must support another enumeration. Our string[] does.

The examples assume at least one ID. For an empty inclusion filter, return an empty result before executing. For an exclusion filter, preserve its different meaning: exclude nothing. Keep that decision in the calling code rather than hiding it inside the row iterator.

Measure it where the work happens

This is useful when TVP preparation is a meaningful part of a busy application’s allocations. It is not a reason to replace every DataTable, and it does not fix an expensive SQL query plan.

In a local run with .NET 10.0.11 and SqlClient 7.0.3, constructing and consuming 10,000 IDs allocated:

The input strings were created before measurement. The SQL Server 2025 container run (17.00.4085) still allocated 473.22 KiB per operation for the reused-record path at 10,000 IDs, because command and driver work remain. It sends the TVP over an already-open connection and executes SELECT COUNT_BIG(*) FROM @Ids; container startup and schema creation are outside measurement. Creating a fresh record for every ID actually allocated more than DataTable in this run.

Both suites used one launch, three warmups and five measured iterations. These short local runs are an allocation observation, not a production latency promise. Measure the complete operation with your ID counts, concurrency and query. A benchmark that only calls Records(ids) without enumerating it misses the work.

SQL Server does not maintain column statistics for TVPs. If large input sets produce poor query plans, examine that separately; a temporary table may be a better fit. Reducing client allocations and improving server execution are different changes.

Useful links

• Microsoft: using table-valued parameters — setup and supported input types.
• SqlDataRecord and SqlMetaData constructors — column setters, lengths, precision and scale.
• SQL Server and .NET type mappings — the reference for other column types.
• SQL Server capacity limits — including the parameter limit.