Are Native and Open SQL competitors? The answer is simple. No. They have their own identity and usage.
Native Syntax in a nutshell:
ABAPers would not like if someone tells them that they are not the real SQL developer. After all, ABAPer rarely deal with Native SQL. Native SQL is considered the real SQL for the database in use.
If you see any code between EXEC SQL — ENDEXEC. It is Native SQL syntax.
What are the possible reasons for adopting Native SQL Approach?
Answer:
i) Access tables that are not available on DDIC layer. So, we have not choice but to use Native SQL.
ii) To use some of the special features supported by DB-Specific SQL, like passing hints to Oracle Optimizer (for the index which boosts performance) etc.
What are the Pitfalls of Native SQL?
Answer:
i) One of the not so good properties of Native SQL is that they are loosely integrated into ABAP.
ii) No syntax check at compile time for Native SQL. Statements are directly sent to the database system. Handle exception CX_SQL_EXCEPTION
iii) No automatic client handling, no table buffering.
iv) All tables, in all schemas, can be accessed.
The above drawbacks mean, Developers are responsible for client handling and accessing correct schema. Developers need to take care of releasing DB resources, proper locking and handle the COMMIT efficiently.
Open SQL in a nutshell:
SAP says:
Open SQL consists of a set of ABAP statements that perform operations on the central database in the SAP Web AS ABAP. It is called Open because it is database independent. Open = Platform independent.
Open SQL is the only DB abstraction layer with an SQL-like syntax that defines a common semantic for all SAP-supported databases. Behind the scene, the Kernel programs are busy converting the Open SQL statement to Native SQL statements for the database in use.
Open SQL can only work with database tables that have been created in the ABAP Dictionary.
Open SQL supports more standard SQL features (SQL92)
i) Some limitations of Open SQL removed starting with ABAP 7.4 SP05.
ii) For SAP HANA and other database platforms.
Open SQL supports Code Push down
i) Push down data intense computations and calculations to the HANA DB layer
ii) Avoid bringing all the data to the ABAP layer.
According to SAP, “Code Pushdown” begins with Open SQL
i) Use aggregate functions where relevant instead of doing the aggregations in the ABAP layer
ii) Use arithmetic and string expressions within Open SQL statements
iii) Use computed columns in order to push down computations that would otherwise be done in long loops.
iv) Use CASE and/or IF..ELSE expressions within the Open SQL.
If you have already read the above points somewhere else, then please ignore it. Check the below tables for a quick comparison of Native and Open SQL. I am sure, you have not seen such handy tables elsewhere.
Difference between Native SQL and Open SQL
| Seq No | Parameters | Native SQL | Open SQL |
| 1 | Compilation at ABAP layer | No | Yes |
| 2 | Database dependency | Yes | No |
| 3 | Table buffering possible | No | Yes |
| 4 | All Schema Access | Yes | No |
| 5 | Access ABAP Dictionary | No | Yes |
| 6 | Access to ABAP Core Data Services views | No | Yes |
| 7 | Conversion of SQL statements to new syntax without any side effect | No | Yes |
| 8 | Possibility of limiting the result set using ‘UP TO’ statement | No | Yes |
| 9 | “Keep unnecessary load away from DB | No | Yes |
| 10 | Possibility of Secondary Index | No | Yes |
| 11 | Comparatively faster Aggregation and Calculation | Yes | No |
| 12 | Strict Syntax check | No | Yes |
| 13 | Consumption of parameterized CDS views | No | Yes |
| 14 | Mandatory use of EXEC SQL statement | Yes | No |
Similarity between Native SQL and Open SQL
| Seq No | Parameters | Native SQL | Open SQL | Comments |
| 1 | All JOINs availability | Yes | Yes | Left, Right, Inner & Outer Joins |
| 2 | String operations availability | Yes | Yes | |
| 3 | Arithmetic Expressions | Yes | Yes | |
| 4 | Case Expressions | Yes | Yes | |
| 5 | Usage of UNION and UNION ALL | Yes | Yes | |
| 6 | Supports Aggregation, Joins And Sub-Queries | Yes | Yes | |
| 7 | Code Pushdown | Yes- via Database Procedures | Yes – via AMDP | |
| 8 | Usage of computed columns to avoid loops – eg: Aggregation and Summation | Yes | Yes | |
| 9 | Recommendation of using specific fields than using SELECT* statement. | Yes | Yes |
If you have never written a Native SQL code before, please refer to the Native SQL Example Code Snippet below. Please do not ask, why did we not use Open SQL. This is just an example, my friend. Ideally, we should not be writing Native SQL for EKPO table which is available in DDIC layer.
Till I get a real database table example, be happy with this EXEC SQL — ENDEXEC statement.
TYPES: BEGIN OF ty_ekpo,
ebeln TYPE ebeln,
ebelp TYPE ebelp,
werks TYPE werks_d,
END OF ty_ekpo.
DATA: wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ekpo.
PARAMETERS p_werks TYPE werks_d.
*--------------------------------------------------------------------*
* Native SQL Begin
*--------------------------------------------------------------------*
EXEC SQL PERFORMING loop_and_write_output.
SELECT EBELN, EBELP, WERKS
INTO :wa_ekpo
FROM ekpo
WHERE werks = :p_werks
ENDEXEC.
*--------------------------------------------------------------------*
* Native SQL End
*--------------------------------------------------------------------*
* Subroutine from Native SQL
FORM loop_and_write_output.
WRITE: / wa_ekpo-ebeln, wa_ekpo-ebelp, wa_ekpo-werks.
ENDFORM.Let us see some output.
Try to put some wrong syntax between EXEC SQL and ENDEXEC. Syntax checker would not catch it and your program would activate successfully, but your program might dump. Do it yourself and have fun.
