SQL Server / Advanced / Lesson 18

Application access and resilient workflows

Integrate SQL Server into a reliable application boundary. Combine typed commands, bounded pools, idempotency, optimistic edits, pagination, and transaction-aware failure handling.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Define a safe driver command contract.
Explain uncertain commit outcomes and durable idempotency.
Use stable keyset pagination and an outbox boundary.

Bound connections and consume results

Open a connection when needed and reliably close or dispose it so the provider can return it to the pool. Pooling reuses physical connections; it does not make leaked logical connections harmless. Keep result consumption bounded and release readers promptly. A slow consumer can hold resources and contribute to server waits even after the query starts returning rows.

Configure pool size, connection timeout, command timeout, and cancellation according to a measured application budget. Increasing the pool without considering server capacity can amplify overload. Failures after cancellation still need transaction cleanup and a clear connection policy. Transport encryption and certificate validation belong to driver configuration; do not disable validation as a routine connectivity fix.

Bind exact values and inspect shape

Use driver parameters with explicit SQL Server types and suitable lengths or decimal precision and scale. Avoid type inference that turns a numeric key into text or supplies varying string lengths that complicate plan reuse. A local sp_executesql example demonstrates a date and Unicode string contract; the application must implement equivalent binding through its provider, with no GO in command text.

Check expected result columns and affected counts. Use DBNull or the provider's documented null representation rather than the literal text 'NULL'. Keep authorization independent from binding, and select dynamic identifiers only from approved mappings. A database API should document success, business rejection, concurrency conflict, transient failure, and uncertain outcome explicitly.

sql A Unicode-and-date command contract

DECLARE @label nvarchar(40)=N'طلب تجريبي';
DECLARE @day date=DATEFROMPARTS(2026,1,15);
EXEC sys.sp_executesql
 N'SELECT @name AS label,@created AS created_on;',
 N'@name nvarchar(40),@created date',
 @name=@label,@created=@day;

Expected output

label       created_on
طلب تجريبي  2026-01-15

The date is a typed value, not culture-dependent text. The label remains Unicode throughout binding.

Make repeated requests refer to one operation

A client-generated operation key with a database UNIQUE constraint can identify one intended order across retries. Within one transaction, persist the request key, validated payload identity, order changes, and stored response information. A duplicate key should lead to retrieving or reconciling the original operation, not blindly creating a second order. Reject reuse of the same key with a different payload.

The exact concurrency implementation must handle simultaneous first attempts; a preliminary EXISTS alone is not sufficient. Use a reviewed uniqueness-based or locking design. If the connection is lost during COMMIT, the server may have committed even though the client saw an error. Query by the same durable operation key to learn the result before deciding to retry. A confirmed 1205 rollback is a different case.

Page with a complete order contract

For a stable increasing key, keyset pagination requests keys greater than the last seen key with TOP and ORDER BY. It avoids scanning increasingly large offsets for a simple next-page workflow. The example returns identifiers 3 and 4 after 2. Real filters and sorting may require a composite continuation token containing every ordering component.

Keyset pagination is not automatically a snapshot of a changing data set. Inserts, deletes, and updates can affect later pages; define whether the product expects a live feed or a consistent export. OFFSET/FETCH also requires a unique order for stable results and can shift when earlier rows change. Treat pagination consistency and authorization as part of the API contract.

sql A simple keyset page

DECLARE @lastId int=2,@pageSize int=2;
SELECT TOP (@pageSize) OrderId
FROM (VALUES (1),(2),(3),(4),(5))v(OrderId)
WHERE OrderId>@lastId
ORDER BY OrderId;

Expected output

OrderId
3
4

The continuation token for this toy ordering is 4. Production ordering needs all sort keys and a defined change policy.

Coordinate database state and external effects

For order confirmation, commit the order and an outbox event in the same database transaction. A separate worker delivers events and records progress with a deduplication design, because delivery can occur more than once. This avoids claiming that a SQL rollback can recall an email or reverse a remote payment. The consumer also needs an idempotency contract.

Review the full workflow: trusted tenant scope, typed parameters, positive quantity validation, guarded inventory update, durable request key, transaction cleanup, outbox record, and a stable response. Test concurrent submissions, duplicate keys with matching and mismatched payloads, 1205 retries, timeouts, and lost confirmation. The application boundary is complete only when success and ambiguity are both handled deliberately.

Common errors and fixes

The connection pool is exhausted.

Why it happens: Readers or connections remain held, or concurrency exceeds capacity.
How to fix it: Ensure deterministic disposal and investigate request duration before resizing.
Prevent it next time: Load-test bounded resource use.

Retry created two orders.

Why it happens: An uncertain commit was repeated without a durable operation identity.
How to fix it: Reconcile through the original unique request key.
Prevent it next time: Design idempotency before transient retries.

Emails duplicated after worker restart.

Why it happens: Delivery was assumed to be exactly once.
How to fix it: Use consumer deduplication and durable progress semantics.
Prevent it next time: Test interruption between delivery and acknowledgement.

Check your understanding

1. A client times out while COMMIT is in flight. Should it create a fresh request key?

Show worked solution

No. Preserve the original key and reconcile the durable result. A fresh key can represent a second business operation even if the first already committed. Handle unknown outcomes separately from confirmed rollback.

2. An outbox worker sends an event and crashes before recording completion. What must the consumer support?