Does SQL Server cache data results for DAO?

Question

I was surprised to be confronted with the idea that using .NET and DAO one could make use of the SQL Server data cache in a way not at all possible with stored procedures (which benefit from the plan cache). I.e. that the query results would be cached and your .NET application run much faster due to this. Is this non-sense? There seems to be some sort of caching for DAO but that doesn't seem to be really relevant in this case.

I've read this question, but it doesn't really answer the question, because it's about stored procedures only.

I hope I post this in the right place. Any help appreciated.

Answer 1

While I would usually leave an answer this short as a comment, this seems to be worthy of breaking with good practice.

No. Nonsense. Absolutely not.

The answer that @gbn gave to the question you reference is valid, regardless of the method of query. Stored procedure or adhoc, the same applies... Query results are not cached.

However, the source table and index data and metadata will be cached after the 1st use (subject to continued use, load and memory pressure though)

That is, the results of a query will be evaluated every execution but the tables(s) (and any indexes etc) used by the query will most likely be in memory already.

The "some sort of caching for DAO" is client/API behaviour, irrelevant and unbeknownst to SQL Server.