One of the most underrated features in SQL Server is the user‑defined table type. Instead of passing CSV strings or looping through rows, you can send an entire set of structured data into a stored procedure.
🔍 What They Are
A table type defines a schema (columns, constraints) for a table variable.
Procedures/functions can accept this type as a parameter (READONLY).
Internally, SQL Server materializes it as a table variable in tempdb during execution.
⚙️ How They Work Internally
When you pass a table type parameter, SQL Server creates a table variable instance based on the type definition.
Data is stored in 8 KB pages like any other table.
Small sets → kept in memory; large sets → spilled into tempdb.
Optimizer treats it like a table variable, but with limited statistics (often assumes 1 row).
Constraints (like primary keys/unique) act as indexes, but you can’t add non‑clustered indexes dynamically.
⚡ Why They’re Useful
Batch operations: Insert/update multiple rows in one call.
Strong typing: Procedure knows exactly what columns to expect.
Reusable: Define once, use across multiple procedures.
⚖️ Trade‑offs
Always READONLY → you can’t modify the parameter inside the procedure.
No full statistics → optimizer may misestimate row counts for large sets.
Limited indexing → only PK/Unique constraints allowed.
Performance → great for small/medium sets; for very large sets, temp tables often perform better.
Top comments (0)