Are Common Table Expression's (CTE) available

问题:

I recently found the following article:

http://www.tsqltutorials.com/with-common-table-expressions.php

The article doesn't list which version of SQL server this became available in. Does this work in SQL Server 2000 and if not what is the earliest version that it is usable in?

Update: I did try a quick test and the test doesn't work. I'm asking that it doesn't work in SS2000 to ensure it isn't me or my setup.

回答1:

Common table expressions were introduced in SQL Server 2005.

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

回答2:

If you need that functionality, it might be a good way to convince management to upgrade.

回答3:

@Aaron - you said, "Compatability level doesn't affect syntax availability." Actually, I think that's only true sometimes. What I've seen using a compatibility level 80 (SQL Server 2000 mode) database in SQL Server 2005 is that some syntax that is new to SQL Server 2005 is available, other is not. For instance, as you rightly noted, CTEs are available. However, the PIVOT operator is not. So (in my little experience) it seems to depend on the particular syntactical feature. (I would post this under the top answer, but apparently this site by design requires that you get 50 reputation points before you can respond in the thread-appropriate location.)

回答4:

Be careful with compatibility levels and CTE, they're a little odd.

SELECT 1 a
WITH A AS (SELECT 1 a)
    SELECT 1 from A

Runs on SQL Server 2005 compatibility level 80 (SQL Server 2000), but not compatibility level 90 (SQL Server 2005). Compatibility level 90 correctly requires a semicolon before the with.

As noted before SQL Server 2000 doesn't support this at all.

回答5:

Running SQL 2008 with compatibility level set to 80 and CTE seems to work. Real bizarre. Don't ask why we have compatibility level set to 80... just started here.