Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation?
Additional Context I've always given the advice that if using computed columns you should use the PERSISTED option unless you're expecting a higher insert/update frequency than read frequency, or if you need better performance around inserts/updates than you do reads of the data (i.e. since the cost of calculation has to be incurred on one or the other, so the decision is really where you want to pay that cost).
There's also the consideration of the additional storage for the computed data, but typically that's pretty negligible and cheap, so not much of a consideration.
However, I wanted to check if my advice is entirely accurate, since SQL may be more intelligent... i.e. Once SQL's calculated a computed column, it could record this value in memory so that it doesn't have to recalculate the value on subsequent queries. SQL could have a timestamp on the cached value and another on the underlying record data to say if that record's been changed since the computed value was calculated to determine whether the cached value is still valid.
Is there anything like this in place / does it depend on available resources (e.g. memory), or other factors (e.g. does the cached value have a TTL beyond that of the process lifetime)? I've never read anything implying that this exists, but I'd be surprised if there weren't some optimization going on under the covers.