# SQL Server Rule: SRP0014
| | |
|----|----|
| Assembly | SqlServer.Rules |
| Namespace | SqlServer.Rules.Performance |
| Class | AvoidTableVariableInJoinsRule |
## Rule Information
| | |
|----|----|
| Id | SRP0014 |
| Friendly Name | Table variable in JOIN |
| Category | Performance |
| Ignorable | true |
| Applicable Types | Procedure |
| | Scalar Function |
| | Table Valued Function |
## Description
Avoid the use of table variables in join clauses.
## Summary
Avoid the use of table variables in join clauses planning and maintenace hazard.
### Remarks
<list type="bullet">
<item> Execution plan choices may not be optimal or stable when a table variable contains a
large amount of data ( above 100 rows).</item>
<item>
Table variables are not supported in the SQL Server optimizer's cost-based reasoning
model. Therefore, they should not be used when cost-based choices are required to
achieve an efficient query plan. Temporary tables are preferred when cost-based
choices are required. This typically includes queries with joins, parallelism
decisions, and index selection choices. </item>
<item> Queries that modify table variables do not generate parallel query execution
plans. Performance can be affected when very large table variables, or table variables
in complex queries, are modified. In these situations, consider using temporary tables
instead. Queries that read table variables without modifying them can still be
parallelized.</item>
<item> Indexes cannot be created explicitly on table variables, and no statistics are
kept on table variables. In some cases, performance may improve by using temporary
tables instead, which support indexes and statistics.</item>
<item> CHECK constraints, DEFAULT values and computed columns in the table type
declaration cannot call user-defined functions.</item>
<item> Assignment operation between table variables is not supported.</item>
<item> Because table variables have limited scope and are not part of the persistent
database, they are not affected by transaction rollbacks.</item>
<item> Table variables cannot be altered after creation.</item>
</list>
<sub><sup>Generated by a tool</sup></sub>
