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.
Common table expressions were introduced in SQL Server 2005.
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/
If you need that functionality, it might be a good way to convince management to upgrade.
@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.)
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.