Does SQL Server support the Oracle-like WITH clause?

Question

I've seen mention of the Oracle WITH clause a few times around here for aliasing a subquery like this:

WITH myData AS (SELECT id, text FROM SomeTable)
SELECT myData from SomeOtherTable

Does any version of SQL Server support this? If not, is there a particular reason they don't? Performance? Potential for incorrect usage?

Answer 1

Yes SQL2005 and SQL2008 both support this. They are called Common Table Expressions.

Answer 2

SQL Server 2005 and up.

I wanted to add that you can stack these to good effect:

WITH A AS (
    SELECT * FROM X
), B AS (
    SELECT * FROM A
), C AS (
    SELECT * FROM B
)
SELECT * FROM C

You can even do:

WITH A AS (
), B AS (
)
SELECT * FROM A INNER JOIN B ON whatever

Also note that WITH must be the first keyword in a statement, so you often see it written as:

;WITH A AS (
)

Which basically terminates the previous statement (semicolons are kind of optional in T-SQL)