> ## Content Index
> Fetch the complete content index at: https://devinside.cloud/llms.txt
> Use this file to discover other available public pages before exploring further.

# One result row can hide a large scan.
- URL: https://devinside.cloud/one-row-returned-a-large-intermediate-workload/
- Published: 2026-11-12T15:49:29.000Z
- Updated: 2026-11-13T00:58:22.000Z
- Description: The endpoint returns one JSON object: a count and a total for a day. The response is tiny, so the database work must be tiny too.
- Author: Marcin Borecki
- Tags: Engineering, Data and performance, #eng26, #devinside-review

Historical edition · Sources and examples scoped to 2026-04-09.

The endpoint returns one JSON object: a count and a total for a day. The response is tiny, so the database work must be tiny too.

That inference confuses the last boundary with every boundary before it. The database may inspect a broad timestamp range, evaluate a conversion on many rows, group an intermediate set and finally return one aggregate row. The client sees the answer, not the path used to obtain it.

Our worked example is a generated event ledger. Every event has an instant and an amount. A request names a calendar day and an offset rule. The model compares a full inspection with a range chosen for that day, while keeping the qualifying set and aggregate independently visible.

Change the day, offset and selected access strategy. Watch the boundary move across events near midnight. An apparently faster predicate is not an optimization if it answers a different definition of the day.

The access strategies are declared by the visual. They are not predictions of what SQL Server's optimizer must choose for a particular expression. The engine can transform some predicates, including some conversions; the actual plan decides what work happened in a real execution.

## Define the day before rewriting the predicate

“Orders on Tuesday” needs a time basis. Does the stored value represent UTC, a local wall time without an offset, or an offset-aware instant? Does the product mean the caller's local day or the server's UTC day? Those choices can change the accepted set.

A half-open interval is a useful representation once the basis is known. It includes the beginning and excludes the next beginning, avoiding assumptions about the final representable fractional second of the day.

```sql
WHERE OccurredAt >= @start
  AND OccurredAt <  @end

```

The parameter types must match the intended storage and semantics. If `OccurredAt` is `datetime2` containing UTC values by application convention, both boundaries should be UTC values in that same convention. If the column is `datetimeoffset`, preserve the corresponding instant semantics deliberately.

![A local day can cross the UTC date boundary](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/day-boundary.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/day-boundary.svg)Swipe the drawing to inspect it.

The visual uses a fixed numeric offset to keep the arithmetic inspectable. Real time zones can change offset during a day, so a local day is not always a fixed twenty-four-hour UTC interval. A production conversion needs the named zone's rules and explicit handling of ambiguous or invalid local times where relevant.

## A range can expose an access opportunity

With an index ordered by `OccurredAt`, an interval can correspond to a contiguous range. A predicate written as a function of each value can have a different relationship to that ordering.

```sql
-- A date projection expresses the requested condition.
WHERE CONVERT(date, OccurredAt) = @day;

-- A half-open range expresses explicit boundaries in the stored basis.
WHERE OccurredAt >= @start AND OccurredAt < @end;

```

These forms are not universally interchangeable. They must first agree about the time basis, parameter types and domain. Nor does the first form universally force a scan: optimizer transformations can produce an access range for some cases. The correct statement is that the rewrite exposes a specific interval whose actual execution still needs inspection.

The [historical CAST/CONVERT reference](https://github.com/MicrosoftDocs/sql-docs/blob/31055611a327387a4b3b7312a28dbf337eb08f81/docs/t-sql/functions/cast-and-convert-transact-sql.md?ref=devinside.cloud) is frozen before this edition's context. A textual predicate is a specification input to the optimizer, not a guarantee of one physical operator.

![Equivalent intent still needs actual-plan evidence](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/predicate-shapes.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/predicate-shapes.svg)Swipe the drawing to inspect it.

## Bind types instead of relying on a plausible string

A query parameter should carry the intended SQL type and value. Passing an untyped or differently typed string can introduce conversion behavior, culture assumptions or implicit conversions that were absent from the intended contract.

```csharp
using System.Data;
using Microsoft.Data.SqlClient;

var startParameter = new SqlParameter("@start", SqlDbType.DateTime2)
{
    Value = startUtc
};
var endParameter = new SqlParameter("@end", SqlDbType.DateTime2)
{
    Value = endUtc
};

```

The typed-parameter example uses Microsoft.Data.SqlClient 5.1.6, whose [NuGet publication record](https://api.nuget.org/v3/registration5-semver1/microsoft.data.sqlclient/5.1.6.json?ref=devinside.cloud) predates this edition. The code-check companion verifies the date boundaries and parameter construction without opening a database connection.

This is an adapter fragment for a `datetime2` UTC convention, not a universal prescription for all date columns. The application must establish that convention. A type named `DateTime` does not, by itself, explain whether an unspecified `Kind` is being interpreted consistently across layers.

If the client accepts an offset-aware request, derive the interval once at a defined boundary and pass the resulting values through the query adapter. Recomputing it independently in multiple layers creates opportunities for subtle differences around midnight or zone transitions.

## Keep three counts in the report

The number of source rows encountered, the number of source rows qualifying and the number of rows returned are distinct. For a scalar aggregate, the final response can remain one row while either earlier count changes substantially.

The model paints every candidate as it is inspected. Qualifying events contribute their amount; the aggregate emits one result at the end. Switch the declared strategy from range lookup to full inspection. The expected count and amount remain the same while candidate work changes.

![One result row does not count intermediate work](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/aggregate-boundaries.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/aggregate-boundaries.svg)Swipe the drawing to inspect it.

A real plan can also contain other intermediate work that this model excludes. A join can multiply rows before an aggregate reduces them. A lookup can repeat for many candidates. A sort or hash operation can use memory and temporary storage. The final row is therefore a correctness contract, never an adequate cost estimate.

## Read actual properties, not just the SQL text

Capture the actual execution plan and I/O observations with the parameters that produced them. The versioned references for [STATISTICS XML](https://github.com/MicrosoftDocs/sql-docs/blob/31055611a327387a4b3b7312a28dbf337eb08f81/docs/t-sql/statements/set-statistics-xml-transact-sql.md?ref=devinside.cloud) and [STATISTICS IO](https://github.com/MicrosoftDocs/sql-docs/blob/31055611a327387a4b3b7312a28dbf337eb08f81/docs/t-sql/statements/set-statistics-io-transact-sql.md?ref=devinside.cloud) define those collection boundaries.

```sql
SET STATISTICS IO ON;
SET STATISTICS XML ON;

SELECT COUNT_BIG(*) AS EventCount, COALESCE(SUM(Amount), 0) AS TotalAmount
FROM dbo.Events
WHERE OccurredAt >= @start AND OccurredAt < @end;

SET STATISTICS XML OFF;
SET STATISTICS IO OFF;

```

The fixture uses a non-null `Amount` column. An empty day has `EventCount = 0` and `TotalAmount = 0`; without `COALESCE`, SQL would return `NULL` for the sum. Make that empty-day pair an explicit result assertion before comparing access plans.

Check the returned values first. Then inspect seek and residual predicates, rows read and rows produced at the relevant operators. Keep per-thread information associated with its operator rather than summing every descendant counter into an inflated total.

A warm scan can still perform substantial logical work even if physical storage access is small in that run. A small client result can hide that work again. Use counters with their actual definitions rather than treating every “read” number as the same unit.

![Connect the parameter to the observed execution](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/inspect-boundary.svg)

[Inspect the full diagram ↗](https://devinside.cloud/content/images/2026/11/devinside/v4/ENG26/inspect-boundary.svg)Swipe the drawing to inspect it.

## Estimates describe the optimizer's expectation

An estimated row count and an actual count answer different questions. A large difference can motivate investigation into statistics, parameter distribution or the expression being estimated. It is not automatically evidence that a particular index or hint is the correct repair.

Conversely, an accurate estimate can describe a legitimately expensive query. If the application asks for most of the table, a broad access path may be reasonable. Optimizing a selective-day example does not establish the right plan for a month or an entire history.

The model lets the selected interval move through a small dataset so the qualifying set is visible. In a real workload, preserve the distribution of request windows. A single favorable parameter can make a rewrite look universally attractive while concealing the cases that dominate production work.

## Test the boundary that could change the answer

Build a small oracle with events exactly at the start, immediately before the end, exactly at the end and on either side of a local-midnight conversion. Include an empty day. The half-open interval should include and exclude the intended identities without relying on a particular timestamp precision trick.

Then compare the aggregate produced by each candidate query against that oracle. If two forms disagree, investigate semantics before comparing elapsed time. The “faster” path may simply exclude work that the user expected in the answer.

The current laboratory companion retains actual SQL plans and measurements separately. No later database build, row-count observation or stopwatch result is presented here as evidence already obtained at the historical context date.

## Change the workload before generalizing the lesson

Try a broader interval, another time basis, skewed event density and a result shape that returns individual records rather than one aggregate. Check whether the access opportunity and covering behavior remain relevant. A larger response can move cost into result consumption and serialization even when the predicate is efficient.

The engineering lesson is not that one syntax is always good and another always bad. It is that the final answer must be defined precisely, the intermediate work must be observed, and the comparison must keep those two responsibilities separate. One returned row can be exactly the right API while still deserving a serious database investigation.

GO FURTHER

## Inspect the contemporary laboratory.

This is a newly reconstructed historical edition, not a claim of earlier authorship. Later executed experiments, their exact environments and original results are preserved in a separate notebook.

[Open the current laboratory notebook ↗](https://devinside.cloud/lab-notebooks/v4/ENG26.html)

[Download the current reconstruction sources ↗](https://devinside.cloud/content/files/v4/ENG26-reconstruction.zip)