Cardinality Estimation - Table Variable Vs Temporary Table in SQL Server

SQL Server creates and maintains statistics for temporary tables, which lead to better cardinality estimation and optimal execution plan generation. Table variables have no statistics, which can result in poor cardinality estimation and non-optimal execution plan creation. When you add query hint option (recompile) when using table variable, SQL Server can use heuristics based estimates, taking into consideration the total number of rows in the table variable; this can be helpful in some scenarios.

The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

Attend our upcoming SQL Server Performance Tuning Hands-on Workshop in London! Or check out other workshop locations ~

Subscribe to our Newsletter