Common table expression

• AS keyword in the same line with WITH keyword

  WITH mycte(x) AS (SELECT x = Convert( VARCHAR(1000), 'hello' ) 
                    UNION ALL  
                    SELECT Convert( VARCHAR(1000), x + 'a' )  
                    FROM   mycte  
                    WHERE  Len( x ) < 10 
                    UNION ALL  
                    SELECT Convert( VARCHAR(1000), x + 'b' )  
                    FROM   mycte  
                    WHERE  Len( x ) < 10) 
  SELECT x  
  FROM   mycte  
  ORDER  BY Len( x ), 
            x;  

• AS keyword in new line

  WITH mycte(x) 
       AS (SELECT x = Convert( VARCHAR(1000), 'hello' ) 
           UNION ALL  
           SELECT Convert( VARCHAR(1000), x + 'a' )  
           FROM   mycte  
           WHERE  Len( x ) < 10 
           UNION ALL  
           SELECT Convert( VARCHAR(1000), x + 'b' )  
           FROM   mycte  
           WHERE  Len( x ) < 10) 
  SELECT x  
  FROM   mycte  
  ORDER  BY Len( x ), 
            x;