Syntax for Common Table Expression from Books online
expression_name [ ( column_name [ ,...n ] ) ]
( CTE_query_definition )
Common Table Expression Purpose
- WITH just represents an expression.
- CTE is not a table, nor a view, it's only an expression.
- The expression can reference itself.
- CTE is designed for recursions.
- CTE is most often used to display hierarchical lists.
- The performance of the CTE is often better than creating and using #temp tables. [This is a generalization, check it with the stats]
Common Table Expression Restrictions
- Common Table Expression do not support constraints like primary keys, unique, not null, defaults...
- There is no logging of any operation. [This can be an advantage for performance]
- There is no locking of resources. [This can be an advantage for performance]
- CTE exists for only the one query statement currently executing.
- CTE exists only as READ-ONLY.