Azure Synapse Result Set Caching Eligibility
You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool named Pool1. You have the queries shown in the following table. You are evaluating whether to enable result set caching for Pool1. Which query results will be cached if result set caching is enabled? - 
Community Votes
75% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
The core concept is that result set caching only stores results for deterministic queries; the common trap is assuming all simple SELECT statements are cached without checking for hidden non-deterministic elements like GETDATE() or UDFs.
This question tests knowledge of Azure Synapse Dedicated SQL Pool result set caching rules, specifically identifying queries eligible for caching based on determinism. Community consensus confirms that deterministic queries are cached while those using non-deterministic functions or user-defined functions are excluded.
Many candidates incorrectly select options including Query2 or Query3 because they overlook that these queries likely use non-deterministic built-in functions (like GETDATE()) or User-Defined Functions (UDFs), which explicitly disqualify them from caching.
Community Discussion (5 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Result set caching in Azure Synapse Dedicated SQL Pool improves query performance by storing the results of SELECT queries so they can be retrieved quickly if the same query runs again and the underlying data hasn't changed. However, caching is strictly limited to deterministic queries. Query1 uses deterministic expressions and base table data, making it eligible for caching. Therefore, Query1 is cached.Why the Other Options Are Wrong
Query2 and Query3 are ineligible for caching because they contain non-deterministic elements. According to Microsoft documentation, queries using non-deterministic built-in functions (such as GETDATE(), NEWID(), or RAND()) or User-Defined Functions (UDFs) are not cached. This is because their output may change even if the underlying data remains static, rendering a cached result potentially incorrect. Thus, any option suggesting these queries are cached is incorrect.Community Comment Notes
Comment [1] provides a detailed breakdown of what is not cached, emphasizing non-deterministic functions and UDFs. Comment [3] succinctly notes that queries dependent on the 'User' (likely implying session-specific or non-deterministic context) are not cached. Comment [4] cites official examples distinguishing between eligible deterministic queries and ineligible ones using RLS or non-deterministic functions, reinforcing the correct answer C.Official Reference
Exam Strategy
Always check for non-deterministic functions (GETDATE, NEWID, RAND) and UDFs in SELECT statements when evaluating caching eligibility. If a query contains these, it cannot be cached regardless of how simple the logic appears.