This blog post about CROSS APPLY in Sql Server 2005 is fucking cool. At work there's a field in a user table that is a {} delimited list of project IDs to which the user is restricted to logging into. Well, it's impossible to join or filter from a {} delimited list. So, I spent 2 hours yesterday writing a table-valued user-defined function which accepts a delimited list and returns a table. Riding high on my hacking prowess, I entered this into the SQL Management Studio query window:

SELECT *
FROM dbo.Table1 a INNER JOIN dbo.MyFunction(a.ProjectRestrictions) f
ON a.userid = f.key

only to be denied all joy by a syntax error at a.Projectrestrictions.

Now, with CROSS APPLY I could use this query:
SELECT *
FROM dbo.Table1 a cross apply dbo.MyFunction(a.ProjectRestrictions) f
where a.userid = f.userid

Of course, the server in question where this action is taking place is still running SQL Server 2000, so I'm still denied any joy, but I warm myself with the knowledge that what I wanted to do is possible in principle.