Cardinality Estimation - Table Variable Vs Temporary Table in SQL Server

Play Cardinality Estimation - Table Variable Vs Temporary Table in SQL Server
Sign in to queue

Description

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.

Tag:

SQL Server

Embed

Download

Download this episode

The Discussion

Add Your 2 Cents