SAP SQL

HANA supports standard SQL for the usual SELECT/INSERT/UPDATE/DELETE plus rich analytical features (window functions, aggregations, joins). Because HANA is columnar and in-memory, set-based SQL that aggregates and filters in the database is extremely fast, far better than pulling rows out and looping.

A note on dialects, since it causes confusion. HANA SQL is close to the standard and has its own extensions, so a statement written for another database usually works with small changes and a statement using another vendor's proprietary functions does not. Where portability matters, staying close to standard SQL is worth the small loss of convenience.

SQLScript adds procedural constructs (variables, control flow, table variables) so you can implement more complex logic as stored procedures and functions that run inside HANA. This is central to code pushdown: move data-intensive logic to the database instead of the application server.

The distinction that matters inside SQLScript is between declarative and imperative logic. Declarative means table variables assigned from SELECT statements, which the optimiser can see through and parallelise. Imperative means loops, cursors and row-by-row work, which it cannot. Writing SQLScript that loops is writing ABAP in a different language and giving up the reason for being in the database at all.

The guiding principle on HANA is code-to-data: bring the computation to where the data lives (the database) rather than moving large data sets to the application. Well-written SQL/SQLScript and CDS embody this, delivering big performance gains over row-by-row ABAP.

Standard SQL transfers, and a few things are used constantly in this context and are worth being fluent in.

Window functions. ROW_NUMBER, RANK and running totals with OVER (PARTITION BY ... ORDER BY ...). This is how you select the latest record per key, or rank customers by value within a region, in one statement rather than in a loop. It replaces more ABAP than any other single construct.

Common table expressions. WITH clauses that name intermediate results, which makes a complex statement readable and lets the optimiser see the whole thing.

CASE expressions for conditional logic inside a select, so a categorisation happens in the database rather than in a loop afterwards.

Aggregation with GROUP BY and HAVING, which is the cheapest thing HANA does and the thing most often done in ABAP by habit.

Joins, including the difference between an inner and a left outer join, because a missing row from a left join is one of the most common causes of a number being wrong.

Date and string functions, since much of what a report does is formatting and period calculation.

Half an hour, and it is the exercise that makes code-to-data concrete.

1. Take a requirement: for each customer, the most recent order and its value.
2. Write it the procedural way in your head: select all customers, loop, select their orders, sort, take the first. That is one statement plus one per customer.
3. Now write it as a single SQL statement using ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC) in a subquery, filtered to row number one.
4. Run both against a real volume and compare.
5. Read the execution plan for the second. The work happened in the database and one result set came back.

The point is not the syntax. It is that a whole class of ABAP loops has a one-statement equivalent, and recognising which is the skill.

ABAP developers mostly use Open SQL and CDS (which generate HANA SQL), but understanding native HANA SQL/SQLScript helps you write efficient CDS, optimise, and work directly with HANA when needed.

Which route to use follows a clear order. Open SQL first, because it is portable and the compiler checks it. CDS when the logic is reusable or the model is worth naming. AMDP, ABAP-managed database procedures, when you genuinely need SQLScript and want it to live in the ABAP lifecycle. Native SQL last, because it bypasses the compiler and ties the code to the database. Reaching for native SQL first is a common instinct and almost always wrong. See SQL examples for patterns to start from.

Writing a statement is half of it. Knowing whether the database is doing what you intended is the other half, and the plan is where that is visible.

EXPLAIN PLAN shows the intended plan without running the statement. PlanViz shows what actually happened, with timings per operator, and it is the tool for a statement that is slow rather than one you are writing.

What to look for, in order:

Where the filter was applied. Early is right. A plan that reads a whole table and filters at the top has not pushed the predicate down, usually because it was wrapped in a function or a type conversion.

Row counts at each step. A step producing far more rows than expected is where the join went wrong, and it is usually a missing condition rather than a slow operator.

Whether operators ran in parallel. HANA parallelises aggressively, and something running single-threaded often means an imperative construct got in the way.

Time per operator. One dominating step tells you where to look; time spread evenly usually means the volume itself is the problem.

The habit worth forming is checking the plan for anything that will run on real volumes, rather than after somebody complains.

The change of habit that matters is not syntax. It is thinking about a whole set at once rather than about one row at a time, and it takes practice for anyone who learned procedurally.

Procedural thinking asks: for each customer, find their orders, sum them, decide something. Set thinking asks: what is the set of customers with their order totals, and which of those meet the condition. The second is one statement.

The tells that you are still thinking procedurally are a loop with a query inside it, a cursor, a variable accumulating a total, or a temporary table filled row by row.

The constructs that replace them are joins for combining, GROUP BY for accumulating, window functions for anything that needed a position or a previous row, and CASE for decisions. Between them they cover most of what loops were doing.

The practical way to build the habit is to write the procedural version, then ask what the result set looks like and write the statement that produces it directly. After a dozen of those the second version starts arriving first.

• Row-by-row processing instead of set-based SQL.
• Pulling data out rather than pushing logic down.
• Ignoring window/aggregate functions HANA does well.

• Cursors and loops in SQLScript. The optimiser cannot parallelise them, so you have moved the loop rather than removed it.
• Native SQL where Open SQL would do. It gives up type checking and portability for no gain.
• Left joins assumed to be inner ones. See the HANA database and modelling for where these statements end up living.

• Functions wrapped around a filtered column. It prevents the predicate reaching the index or the pruning, and the plan will show the whole table being read.

Writing a select is the easy half, and recognising which loops have a one-statement equivalent is the part you do in the course.

The test of whether the habit has landed: when you catch yourself writing a loop with a query inside it, you should feel it before you finish typing. That reflex is worth more than memorising any particular function.