Views, Transactions and blocking

[wqweto]

Hey, CTE! Everything an App dev wants to see!

HOWEVER: Never use them. Unless doing a recursive call. (You will see this as you scale to larger datasets)

WHY? No statistics. Statistics matter. Query plans and optimization. Can get past this replacing with temp tables (a table has statistics!) instead of CTEs.

Just chime in to say that this makes a lot of sense.

Also "classic" temp tables are different than table variable as these *automatically* get statistics created (if missing) when different query plans are tested/optimized by the query engine.

Table variables and CTEs are more like worktables (internal temporary tables the engine uses for sorting, grouping, etc.) which don't get statistics created on demand. This has a big implication on query plan chosen and ultimately the performance of the final query.

cheers,
</wqw>