When a query can be written in standard SQL, that is where we start, for three fairly down-to-earth reasons: it leaves fewer things tied to one engine, more people can read it, and when the day comes to move that logic somewhere else the job is adapting rather than rewriting. Purity has nothing to do with it, and neither does a promise of speed.
The exceptions exist and are legitimate; they come at the end. First, an example that shows up far more often than anyone would like.
The usual stored procedure
Plenty of companies keep business logic (commissions, balances, period closes) inside stored procedures that walk records one at a time. A cursor picks an agent, looks up their sales, decides a multiplier from their tier, inserts the result, moves on. It has been working for years, which is exactly why nobody wants to touch it.
Trimmed to the essentials, in T-SQL:
-- T-SQL: one agent at a time
OPEN cur_agents;
FETCH NEXT FROM cur_agents INTO @agent_id, @tier, @rate;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @tier = 'GOLD' SET @mult = 1.25;
ELSE IF @tier = 'SILVER' SET @mult = 1.10;
ELSE SET @mult = 1.00;
SELECT @sales = ISNULL(SUM(net_amount), 0)
FROM sales WHERE agent_id = @agent_id;
INSERT INTO commissions (agent_id, amount)
VALUES (@agent_id, @sales * @rate * @mult);
FETCH NEXT FROM cur_agents INTO @agent_id, @tier, @rate;
END
Nothing odd about it; anyone who works with SQL Server regularly reads it without effort. What it does contain is a decision made in advance: we are the ones deciding how the data is traversed, when to query, when to insert, and in what order.
The same logic, written declaratively:
-- ANSI: every agent in one pass
with tiers as (
select agent_id, base_rate,
case tier when 'GOLD' then 1.25
when 'SILVER' then 1.10
else 1.00
end as mult
from agents
where status = 'ACTIVE'
),
agent_sales as (
select agent_id, sum(net_amount) as total
from sales
group by agent_id
)
insert into commissions (agent_id, amount)
select t.agent_id,
coalesce(s.total, 0) * t.base_rate * t.mult
from tiers t
left join agent_sales s on s.agent_id = t.agent_id;
The cursor became a join and the IF/ELSE became a CASE; instead of one operation per agent, we describe the result we want over the whole set, and the business rule does not move an inch.
What the optimizer gets out of it
With the cursor, every agent triggers an aggregation over sales and an
insert. Ten thousand agents means that pattern ten thousand times. In the
declarative version the engine aggregates sales once, resolves the join, and
picks whatever plan it judges best, which is the job it was built for.
There are engines, volumes and cases where a set-based query does not beat a well-built procedure, so this is not a law. What we do hold to is that we would rather hand the optimizer the whole problem than turn the execution into a loop ourselves.
How many people can read it
This benefit tends to get buried under the performance debate, and for us it weighs more.
A query built from joins, aggregations, CASE expressions and named CTEs can be followed by almost anyone who works with data: an analyst opens it, a BI tool runs it, someone from another team reviews it. Once cursors, variables, temp tables and flow control enter the picture, the set of people who can change that logic with confidence shrinks, and over the years that shows up as a cost.
And when it has to move
While everything lives in the same engine, portability feels like a theoretical concern. It stops being one with a migration from SQL Server to another warehouse, with a gradual transition between platforms, or with something far more mundane: wanting to run part of the production logic in a local DuckDB to develop and test.
If most transformations use reasonably standard SQL, you hit isolated
differences and resolve them. If a good share of the logic sits inside
engine-specific procedures, you are no longer adapting queries; you are
reimplementing behavior. That is where a task we know well begins: opening a
five-year-old procedure and trying to work out whether that ELSE 1.00 is a
business decision, a defensive fallback, or something that stayed that way
because it always had.
Pure ANSI does not exist either
“Portable SQL” has a ceiling. As soon as a query does anything mildly
interesting, engines diverge: dates, arrays, JSON, casts, series generation,
analytic functions, pagination. LIMIT, TOP and FETCH FIRST are the
obvious example and nowhere near the only one.
So we do not try to write SQL that copies unchanged across five engines; in practice that is not realistic. The goal is more modest: keep as much of the business logic as possible in the common language, and make the parts that depend on a specific technology explicit and easy to find. Whatever belongs to the engine by nature (partitioning, indexes, distribution, materialized views) is not worth abstracting. The rule is about the logic in the queries.
When we do use the engine’s dialect
When it gives something the standard cannot, and the case truly demands it.
On a recent ClickHouse engagement we used several engine-specific constructs
because they solved concrete problems far better than the generic
alternatives: LIMIT 1 BY to deduplicate in the stream without giving up
ordered index reads, FINAL where consolidated state had to be read,
refreshable materialized views to keep layers fresh. None of it is ANSI, and
we did not pretend otherwise. What separates that from whim is that in each
case we could point to exactly what we gained, measured on our own data.
Before adding an exception we ask a few questions:
- Do we have a real problem to solve, or a feature we happen to like?
- Did we measure the difference on our data, or read it in someone else’s benchmark?
- Does the gain justify taking on a dependency on the engine?
- Can we keep that dependency in a few places, with a note next to it?
- Will it be obvious to someone else why this decision was made?
We do not use a fixed percentage as the line. Fifteen percent is irrelevant in a nightly batch and decisive in a query that runs thousands of times a minute. What we require is that the decision comes from the real case, and that the gain is large enough that nobody has to argue about it.
One signal worries us: an engine feature that starts out solving one case where it genuinely matters and, months later, shows up in every query. At that point you are no longer using the engine selectively; you are building the logic layer around its dialect. That can be a valid call (if the platform will sit at the center of the architecture for years, accepting more lock-in may well be right), but it should be a conscious one, not something you discover afterwards.
What we do with what already exists
We would not rewrite a stored procedure just to be able to say the SQL is more standard now. If it works and causes no trouble, there are more important things to do.
When we do decide to convert one, the condition is simple and not up for negotiation: the new version has to produce the same result. We run both implementations over the same data and compare row counts, keys present and missing, the aggregations that matter, nulls, numeric differences, and the edge cases we already know about. Old procedures accumulate behavior that is documented nowhere; rewriting them without this comparison is the easiest way to change a business rule without noticing.
And we would not start with the most monstrous procedure in the system. Better to look first for one that changes often, that many people read, or that is blocking something else: that is where the payoff shows immediately.
A boring rule, on purpose
We use standard SQL as the starting point because it strikes us as a reasonable default: queries move, get reviewed and get understood with less effort, and each transformation stops being an infrastructure decision. Then we lean on the engine when there is a concrete reason to.
What we are after is that, two years from now, someone can open one of these queries and understand what it does without first having to learn the whole history of the platform it was written on.