I'm not sure how the optimizer would handle them. It would be easy enough to come up with a test query and then execute it via xp_execresultset and a dynamic sql method.
The main advantage of xp_execresultset is that is allows a query to be run in the context of another database without having to switch the current connection to that context. In the article I used FILEPROPERTY which only runs against the current database. I could do this in dynamic SQL (and someone in the forums of Boris Baliner’s article on “Quickly Viewing Available Space” (http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp) did just that) However, the result was quite long and required a lot of context switching.
I don't know if xp_execresultset can take advantage of caching but I wouldn't use it in any situation where that might be necessary in any case. As I stated, this is an undocumented procedure and Microsoft dropped it in SQL 2005. Using this procedure in an application would be ill-advised. I presented it as a tool for administrators as an alternative to writing long dynamic sql-based scripts.
Yes, it executes under the same SPID. To determine this add a ,@@SPID to the end of the query in the script, as shown below:
SET @SQLCmd='SELECT ''SELECT name AS [File],
filename as [File Name],
CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB],
CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used],
CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space] ,@@SPID
"I may not always know what I'm talking about, and you may not either."