Syntax for Common Table Expression from Books online

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

Common Table Expression Purpose

  1. WITH just represents an expression.
  2. CTE is not a table, nor a view, it's only an expression.
  3. The expression can reference itself.
  4. CTE is designed for recursions.
  5. CTE is most often used to display hierarchical lists.
  6. 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

  1. Common Table Expression do not support constraints like primary keys, unique, not null, defaults...
  2. There is no logging of any operation. [This can be an advantage for performance]
  3. There is no locking of resources. [This can be an advantage for performance]
  4. CTE exists for only the one query statement currently executing.
  5. CTE exists only as READ-ONLY.