This guest perspective was authored by Dr. Kerem Koseoglu, the author of SQL: The Practical Guide, available from SAP Press. ASUG members can enjoy 15% off any SAP Press titles with the discount code 15ASUG.
Whether managing large datasets or writing your first query, building SQL programming skills is an important component of mastering SAP systems, particularly given its usage in SAP S/4HANA technologies.
In my SAP Press publication SQL: The Practical Guide, readers can expect a hands-on guide to programming databases with SQL, outlining the basic elements of relational databases as well as providing insights into the syntax, features, and applications of SQL.
Exploring five sublanguages of SQL—DDL, DML, TCL, DQL, and DCL—and how they can be applied for modifying tables and querying data, the book should help readers learn how to master SQL programming for database management and manipulation, also aiding them in learning how to define schemas and tables.
Now, let's start with an introduction.
What is SQL?
SQL—an abbreviation for Structured Query Language—is the industry-standard programming language for relational databases. It is a declarative language that includes commands to manage, manipulate, and query contents of relational database systems. With SQL knowledge, one can handle any relational database task from basic table creation to complex data querying.
The English-like syntax of SQL makes it a language that can be approachable to anyone with decent computer literacy. Check the following SQL statement:
SELECT name, surname, phone
FROM employee
WHERE salary > 60000;
This statement, as many other SQL statements, is easily understandable as plain English. It instructs the database to identify employees whose salary exceeds 60K; and to retrieve their names, departments, and hire dates.
The query may produce a result set similar to the one shown below.
name |
surname |
Phone |
Ethan |
Caldwell |
+1 (312) 555-8392 |
Isabella |
Navarro |
+1 (213) 555-6745 |
Lucas |
Bennett |
+1 (646) 555-2278 |
Sophia |
Ramirez |
+1 (305) 555-9134 |
Easy, right?
Although this basic example features a simple query, SQL is more powerful than that. It consists of several sublanguages that feature similar syntax but are intended for different purposes.
- DDL (Data Definition Language) is used to create, modify or delete data structures, such as schemas, tables, and views. Those structures typically hold or represent data.
- DML (Data Manipulation Language) is used to create, modify or delete data entries in tables.
- TCL (Transaction Control Language) is used to conduct subsequent DML operations as a package, which either executes or cancels completely.
- DQL (Data Query Language) is used to query data from database tables. It is arguably the most widely used sublanguage.
- DCL (Data Control Language) is used for user and authorization management.
When we combine those sub-languages like puzzle pieces, what we get is the complete language called “SQL.”
SQL for SAP: Open SQL, CDS Views, and More
SQL commands are designed with a pre-defined set of standards called ANSI SQL (American National Standards Institute Structured Query Language). Although many database products embrace those standards in general, they typically also deviate from it and add new features on top.
In that regard, SAP is no exception. In fact, SQL syntax even deviates between different SAP technologies. Let’s inspect some of those and understand their differences.
Open SQL
Open SQL is the so-called “old-school” set of ABAP statements, which allow programmers to write SQL commands directly within their ABAP code. Over the years, Open SQL has been modernized with up-to-date features, including those which leverage the power of HANA Database.
It features an enhanced set of DML commands, through which programmers can create, modify, or delete data. DQL is naturally also supported, which means data can be queried in combination with ABAP commands. However, other SQL sub-languages are not directly supported.
Instead, DDL is wrapped with ABAP Development Tools (ADT). Developers are expected to create, modify, or delete data structures via the designated Eclipse integrated development environment (IDE), which contains a base workspace as well as an extensible plug-in system.
TCL, meanwhile, is wrapped with SAP’s internal mechanisms, which offer a simplified approach for ABAP developers using COMMIT / ROLLBACK commands and their variants.
DCL has been replaced with an internal authorization mechanism, where data access is controlled using profiles and roles that are typically maintained by the basis team. ABAP developers can run data access controls using variants of the AUTHORITY-CHECK command.
For a long time, Open SQL was the only way “to SQL” for SAP developers. Now, there are other options, which we'll detail below.
CDS Views
At a high level, a "view" is a virtual table that combines and displays data from underlying real tables. A view doesn’t store data itself but, any time it is called, it gathers corresponding data in real-time and presents them as if the view was itself a real table.
Nearly all database systems have a correspondence for views. SAP R/3 featured an option to create classical views in the SE11 data dictionary. With SAP HANA Database, a new concept called CDS Views was introduced, which has been the SAP approach ever since.
Core Data Services (CDS) Views can be used as regular database views, centralizing complex queries for reusability. But beyond that, they are the backbone of various key SAP technologies, such as ABAP Restful Application Programming (RAP) Model.
ABAP developers can manually code CDS Views within Eclipse IDE. These views provide many features that go beyond what standard ANSI SQL views can achieve. Some examples:
- Currency and unit conversions
- Parent-child hierarchies
- Associations (instead of joins)
- Annotations
- Extensions
- Complex calculations
- Coding support through table functions or custom entities
By its very nature, CDS is designed to read and represent data; therefore, it naturally features an enhanced set of DQL. Where DCL is concerned, CDS views have an optional mechanism to invoke SAP’s internal authorization mechanism, as well as custom authorization checks. Other SQL sublanguages are not part of CDS Views at this time.
AMDP / SqlScript
A stored procedure is essentially an SQL subroutine written for a special task, saved on the database and waiting to be executed. Whenever the stored procedure is executed, all SQL statements within it are processed and executed sequentially.
As with views, nearly all database systems have a correspondence for stored procedures with a custom programming language. Some examples:
- Microsoft SQL Server – T-SQL
- Oracle Database – PL/SQL
- PostgreSQL – PL/pgSQL
SAP HANA databases feature a custom language called SqlScript. This is similar to Open SQL and ANSI SQL in some ways, but don’t assume that any ABAP developer can jump right into it. SqlScript is a distinct programming language of its own, and developers should expect a learning curve.
ABAP developers can code SqlScript right into their ABAP classes using a mechanism called ABAP Managed Database Procedures (AMDP). Once a method is marked as an AMDP method, SAP interprets its code as SqlScript instead of ABAP and handles its HANA Database correspondence automatically.
But why would SqlScript ever be needed, if we have ABAP and its built-in Open SQL at hand? Some significant advantages include:
- Performance. SqlScript executes directly in the database layer of SAP HANA instead of the ABAP application server, which results in vast performance improvements in many cases. Options for parallel execution are also present.
- Reduced code base. Advanced SqlScript commands, such as lateral joins and window functions, are so powerful that a single statement can sometimes replace hundreds of lines of ABAP code involving nested loops.
- Internal table handling. SqlScript allows us to approach internal tables like regular database tables. It is possible to execute SQL commands against internal tables or even join multiple internal tables with database tables.
- CDS empowerment. Table function mechanism allows us to write SqlScript code behind CDS Views, which is crucial in cases where the data processing requirement is simply too complex for CDS features to handle.
SqlScript is also part of SAP’s "code pushdown" practice, where SAP recommends that developers invoke the strength of HANA Database by preferring its mechanisms whenever possible.
Naturally, SqlScript should be seen as a data processing sub-mechanism of ABAP; in other words, it can’t replace ABAP completely. RFCs, BAPIs, batch inputs, object-oriented architectures, unit tests, BADIs, RESTful API calls, ALV, and others are still to be coded using ABAP.
ADBC (ABAP Database Connectivity)
ABAP Database Connectivity (ADBC) is an SAP technology enabling developers to connect to remote databases and execute SQL against them. It is possible for an ABAP developer to pass native SQL statements as strings to the remote database and execute commands or retrieve result sets.
This mechanism opens up space for developers to apply their native SQL skills freely. Depending on the authorization of the ADBC database user, any SQL sub-language can be used on the remote database to fulfill the task at hand. You can, for example, remotely:
- Create remote data structures
- Create or modify remote table entries
- Read remote table entries and retrieve as ABAP internal tables
However, such flexibility comes with increased responsibility. Since you will be operating outside of SAP’s walled garden, security concerns like SQL injection must be considered.
Learning SQL for SAP S/4HANA
As discussed above, SAP has its own approach to SQL. But that doesn’t mean that core SQL skills won’t come in handy – quite the contrary! SAP’s command palette is not a far cry from ANSI SQL; if you are strong on SQL, that strength will give you unique insight into S/4HANA technologies, with some degree of syntax adaptation.
Besides, knowledge of ANSI SQL is also essential when operating outside of the SAP ecosystem. Any relational database-oriented app in Python, Java, or Rust will surely require a strong foundation in SQL. Once you strengthen your core SQL skills, you can make use of them anywhere. In today’s technology world, such transferrable skills are valuable, because there is too much to learn and never enough time to do so.
In SQL: The Practical Guide, from SAP Press, my intention was to provide a trusted guide for your SQL journey. The book begins with an introduction to relational databases and will show you how to set up a local playground database; it also covers DDL, DML, TCL, DQL, and TCL through practical examples. In terms of bonus content, expect entity-relationship diagrams and essential database tips and tricks.
The realistic examples I provide will help readers understand the logic and purpose of SQL features, enabling quick application of SQL to daily tasks. All the case studies are based on real-world scenarios you may encounter during your development journey. I wish you the best of luck with your SQL journey!
Dr. Kerem Koseoglu is a seasoned software engineer, author, and educator with extensive experience in global software development projects. He is the author of SQL: The Practical Guide, available from SAP Press. ASUG members can enjoy 15% off any SAP Press titles with the discount code 15ASUG.