ERPClimb logoERPClimb

SAP Architect Open SQL Interview Questions

Open SQL comes up in SAP Architect interviews because it is one of the few areas where an interviewer can tell, in two questions, whether you have worked with the process or only read about it.

Master Open SQL for fast, clean and HANA-friendly ABAP data access with joins, filters, aggregation, package processing and real project performance patterns.

This page carries 8 reviewed SAP Architect open sql interview questions, each with a complete written answer and no sign-in required. The set is 7 mid-level and 1 advanced — this is a topic interviewers use to separate candidates, so there is no warm-up section.

The fastest way to use this page is to read the question, answer it yourself, and only then read the answer. The gap between your version and the written one is your actual revision list for open sql.

8 Open SQL questions with answers

mediumOpen SQL

1. What is the impact of client handling in Open SQL and how do you deal with cross-client queries safely?

Open SQL adds the current client to the WHERE clause automatically. To read from another client (rare, usually reporting) use CLIENT SPECIFIED and add mandt yourself; you must then guard against accidental cross-client writes. In S/4 the cross-client use case is limited to controlled admin scenarios and should require authorisation checks and audit logging.
mediumOpen SQL

2. A concurrent update is producing occasional data loss on a Z table. How do you detect and prevent it in Open SQL / ABAP?

Classic lost-update: two dialog steps read the row, both compute new values, both UPDATE the same key — the second overwrites the first. Prevent with (1) SAP enqueue lock (ENQUEUE_E_TABLE / dedicated lock object) around read-modify-write, or (2) optimistic locking with a version / changed_at column checked in the WHERE clause of UPDATE and re-tried on zero rows updated. Log the retry to surface hot rows.
mediumOpen SQL

3. What are the risks of SELECT * in production ABAP and how does it interact with HANA column store?

SELECT * transports every column into ABAP; on a column-store DB this defeats one of the biggest performance wins because HANA has to read all columns. It also breaks silently when the DB team adds columns, increases network payload, and prevents index-only reads. Always list the columns you actually consume and align them with a CDS projection view for reuse.
mediumOpen SQL

4. A SELECT ... INTO CORRESPONDING FIELDS OF TABLE runs 3x slower on HANA than on the old DB. Why can that happen?

INTO CORRESPONDING forces a wider projection than necessary because the DB has to return every column the target structure could match, and the copy step in ABAP is more expensive with wide structures. On HANA the round-trip cost is more visible. Fix: SELECT only the columns you need, into a target structure with exactly those fields, or into a CDS-projection type.
mediumOpen SQL

5. What are the golden rules for writing performant Open SQL?

Key rules: (1) select only required fields, not SELECT *; (2) use WHERE with indexed fields; (3) avoid SELECT inside LOOP – use FOR ALL ENTRIES or JOIN; (4) sort/de-duplicate the driver table before FOR ALL ENTRIES and check it is not initial; (5) prefer aggregate functions on DB over LOOP + SUM; (6) use packages / cursors for large volumes; (7) use array operations (INSERT/UPDATE/MODIFY FROM TABLE); (8) push logic into CDS on HANA.
mediumOpen SQL

6. Explain the difference between FOR ALL ENTRIES and a JOIN in Open SQL, and when each is appropriate.

FOR ALL ENTRIES sends the DB a driver list and executes an IN-list style query; it is convenient when the driver is already in memory but risks empty-driver bugs, duplicate rows without a DISTINCT-like effect, and slow execution if the driver is large. JOIN executes fully on the DB and is generally faster on HANA because it avoids the client-server round trip. Prefer JOIN (or a CDS view) when the driver data itself lives in the database; keep FAE for small in-memory drivers.
mediumOpen SQL

7. Given a driver internal table of 100k order IDs, how do you fetch the header rows efficiently?

Sort and delete adjacent duplicates on the key. Guard with IF driver IS NOT INITIAL. Use SELECT ... FOR ALL ENTRIES IN driver into a target table, or better on HANA a proper INNER JOIN with a range table / package-size cursor. Restrict fields to what you need. Watch out for the FAE package-size behaviour (implicit split, so results are unique per package – DISTINCT is not guaranteed).
hardOpen SQL

8. How would you tune a report joining BSEG, BKPF, and a Z-history table that currently takes 45 minutes?

Start with ST05 SQL trace on a representative run to find the slow statement. Typical wins: replace BSEG-secondary-index reads with an index-friendly WHERE that leads with mandt+bukrs+belnr+gjahr; move the join to a CDS view so HANA can push down; project only needed columns; batch driver keys to keep FOR ALL ENTRIES lists reasonable; and check whether the Z-history table needs a new secondary index (with DB team approval). Re-measure after each change.

Related lesson

SELECT SINGLE vs UP TO 1 ROWS

Related topics

Cross-Module Integration and Non-Functional ArchitectureABAP CoreOData GatewayCDS ViewsDebuggingRAP BasicsInternal TablesS/4HANA Clean Core

Next practice step

Practice with Interview AITake a mock test