Press "Enter" to skip to content

Strongly Type Table-Valued Parameters

Jonathan Kehayias shows the benefits of using the MaxLength parameter when calling a table-valued parameter from .NET code:

We can see that the MaxLength for the string columns is set at -1, meaning they are being passed over TDS to SQL Server as LOBs (Large Objects) or essentially as MAX datatyped columns, and this can impact performance in a negative manner. If we change the .NET DataTable definition to be strongly-typed to the schema definition of the user-defined table type as follows and look at the MaxLength of the same column using a debug break:

This can be important, especially if you make a lot of calls or use fairly large TVP sizes.