Datetimeoffset values retain original offset causing cross‑region query mismatch

Direct Answer

For cross-region Azure SQL Database deployments, the recommended strategy depends on whether you can change the storage schema:

• New schemas: Store UTC-only datetime2 (or datetimeoffset with fixed +00:00) and normalize in the application layer before INSERT. This eliminates read-time conversion entirely.
• Existing schemas that must retain original offsets (audit, legal): Add a persisted computed column utc_ts AS (ts AT TIME ZONE 'UTC') PERSISTED. This materializes the UTC value at write time, requires no INSERT changes, and allows indexing for UTC-range queries.
• Ad-hoc queries only: Use AT TIME ZONE 'UTC' in SELECT. Expect <5% overhead for typical OLTP page sizes; cost grows linearly with row count.

Confirmed Behavior

• Azure SQL Database (V12+, SQL Server 2016+) stores datetimeoffset with the supplied offset and returns it unchanged. No server-side automatic UTC conversion exists.
• AT TIME ZONE 'UTC' converts per row at read time (Compute Scalar operator). Benchmarks show low single-digit microseconds per row; <5% overhead vs raw retrieval for typical page sizes.
• A persisted computed column defined as utc_ts AS (ts AT TIME ZONE 'UTC') PERSISTED is deterministic, materializes on INSERT/UPDATE, adds ~8 bytes/row, and does not alter INSERT logic — clients still supply the original offset.
• Indexes and statistics on the persisted column behave like any other column, enabling efficient UTC-range seeks.

Likely Explanation (Not Yet Verified in Your Environment)

• If your workload scans millions of rows per query, the per-row CPU of AT TIME ZONE may become measurable; the persisted column shifts that cost to write time.
• Windows time-zone data underpins AT TIME ZONE; Azure SQL receives regular DST updates, but verify after major tzdata releases if you target non-UTC IANA zones.
• Cross-region application instances must agree on the target zone string ('UTC' vs 'UTC+0' vs IANA name) to avoid mismatches.

Implementation Steps

1. Verify AT TIME ZONE support: SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC' AS utc_now; — should return a datetimeoffset with +00:00 offset.
2. If retaining original offsets, add the persisted column:

   ALTER TABLE dbo.Events
   ADD utc_ts AS (ts AT TIME ZONE 'UTC') PERSISTED;

3. Optionally create a non-clustered index on utc_ts for frequent UTC-range queries.
4. Test with your data volume: SET STATISTICS TIME, IO ON; compare SELECT ts AT TIME ZONE 'UTC' FROM dbo.Events vs SELECT utc_ts FROM dbo.Events at expected row counts.

One Diagnostic Detail Needed

What is the typical row count returned by your cross-region queries? If you regularly scan >100k rows per request, the persisted column becomes strongly preferred; below that, ad-hoc AT TIME ZONE is usually acceptable.