ABAP on SAP HANA. Part XI. Are Native SQL and Open SQL Competitors?

What is the difference between Open SQL and Native SQL

Contributed by: Deepa Subbian, SAP ABAP/HANA Consultant, Melbourne, Australia

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 NoParametersNative SQLOpen SQL
1Compilation at ABAP layerNo
Yes
2Database dependency
Yes
No
3Table buffering possible
No Yes
4All Schema Access
Yes
No
5Access ABAP Dictionary
No Yes
6Access to ABAP Core Data Services views
No Yes
7Conversion of SQL staments to new syntax without any side effect
No Yes
8Possibility of limiting the result set using 'UP TO' statement
No Yes
9"Keep unnecessary load away from DBNo Yes
10Possibility of Secondary Index
No Yes
11Comparitively faster Aggregation and Calculation
Yes
No
12Strict Syntax check
No Yes
13Consumption of parameterized CDS views
No Yes
14Mandatory use of EXEC SQL statement
Yes
No

Similarity between Native SQL and Open SQL

Seq NoParametersNative SQLOpen SQLComments
1All JOINs availability
YesYesLeft, Right, Inner & Outer Joins
2String operations availability
YesYes
3Arithmatic Expressions
YesYes
4Case Expressions
YesYes
5Usgae of UNION and UNION ALL
YesYes
6Supports Aggregation, Joins And Sub-Queries
YesYes
7Code Pushdown
Yes- via Database Procedures
Yes - via AMDP
8Usage of computed columns to avoid loops - eg: Aggregation and Summation
YesYes
9Recommandation of using specific fields then using SELECT* statement.
YesYes

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.

Let us see some output.

Native SQL 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. 🙂

If you want to get more detailed insight about Native and Open SQL. Please visit this sap link.

SAP ABAP for HANA. Part XII. Open SQL, CDS or AMDP, which Code to Data Technique to use?

If you want to get such useful articles directly to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons and like us on facebook.

Do you have anything to add to this article? Have you faced any issue using Native or Open SQL? Do you want to share any real project requirement or solutions? Please do not hold back. Please leave your thoughts in the comment section.

Thank you very much for your time!!

You might like to check the Popular and Effective Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. SAP Adobe Forms Tutorials
3. GOS Tutorials
4. OOPs ABAP Tutorials
5. HANA Tutorials

About the author: Deepa Subbian
Deepa is SAP ABAP consultant having around 6 years experience with more than 1 year of experience in the niche area of ABAP Development on SAP HANA. She is a permanent resident of Australia. Currently, she is residing in Melbourne.

She is Master of Computer Applications by qualification.

Find more about Deepa Subbian at LinkedIn.

Share on Facebook16Share on LinkedIn37Tweet about this on TwitterShare on Google+1Email this to someonePin on Pinterest2Share on Reddit0
Isn't it Fair to Share??