Michael_Garrison (5/30/2014)
1. Why use CTE? I see a number of times when people answer others questions that they use CTE's. I understand for recursive code issues. But for most other code is it not better to use CREATE TABLE and then actually define the data types? Is this not better for the SQL engine to know what the table is then having a CTE and not knowing what data types it uses?
A CTE is nothing more than a named subquery. It is not a table. There's no persistent storage. When you run the query, the performance will be just the same as if you'd used a normal subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability