• Your decision between table variable or temp table should depend on the data access pattern of your query.

    Generally speaking, table variables are best applied in OLTP stored procedures where you want reusable execution plans and avoid recompiles. By default SQL Server estimates the row count of table variables to be 0 or somtimes 1. However, if you're coding heavy duty reporting type queries where cardinality estimation and leveraging alternate execution plans is more important than compile time, then temp tables are better.

    The specific behaviour of table variables versus temp tables is something we need to revisit with each release, and I wouldn't bank too much on it as gospel. NOTE: There is a relatively new trace flag 2453 for SQL Server versions 2012+ that enables cardinality estimation for table variables in some circumstances.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho