Azure Synapse Result Set Caching Eligibility

DP-203 By islever Editorial Last updated

Short answer

C Query1 and Query2 only

Question

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? - Exhibit: Azure Synapse Result Set Caching Eligibility
  1. Query1 only
  2. Query2 only
  3. Query1 and Query2 only Correct Answer
  4. Query1 and Query3 only
  5. Query1, Query2, and Query3 only

Key Takeaways

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.

Common mistake: 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.

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.

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.

Community Votes

C
75%
B
25%

75% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 5 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 5

  1. Tapaskaro 👍 12
    correct What's not cached Once result set caching is turned ON for a database, results are cached for all queries until the cache is full, except for these queries: Queries with built-in functions or runtime expressions that are non-deterministic even when there’s no change in base tables’ data or query. For example, DateTime.Now(), GetDate(). Queries using user defined functions Queries using tables with row level security Queries returning data with row size larger than 64KB Queries returning large data in size (>10GB)
  2. jongert 👍 7
    Correct. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching#whats-not-cached
  3. Alongi 👍 2 Answered C
    Correct, queries that don't depend on the User
  4. f7c717f 👍 1 Answered C
    Answer is "C" as below Examples shows Examples of Eligible and Ineligible Queries Eligible Queries: Queries using deterministic runtime expressions. Queries using deterministic built-in functions. Ineligible Queries: Queries using user-defined functions (UDFs). Queries using row-level security (RLS). Queries using non-deterministic functions (e.g., GETDATE(), NEWID()).
  5. evangelist 👍 1 Answered B
    must be deterministic

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to Verify Result Set Cache Usage in Azure Synapse SQL Pools?

S-grade

Which Diagnostic Setting Tracks Query Start and End Times in Synapse SQL Pool?

S-grade

Which Integration Runtime Connects On-Premises Linux Databases to Azure Data Factory?

S-grade

Which Log Analytics table stores ADF pipeline runs in resource-specific mode?

S-grade

← Back to DP-203 Study Guide