SAP Database

HANA supports standard SQL, transactions (ACID), constraints and the relational model, so it behaves like a database developers know, while adding in-memory speed and advanced engines. For S/4HANA it is the single database holding all application data.

Worth stating plainly for anyone arriving from another database: everything you know about SQL, transactions, constraints and isolation applies. HANA is not a special-purpose store with SQL bolted on. What differs is where the data physically sits and how it is organised, and those differences change performance characteristics rather than semantics.

• Column & row stores for different access patterns.
• Advanced engines: spatial, graph, text/search, predictive/ML libraries.
• SQLScript & procedures for database-side logic (code pushdown).
• Multitenant containers for isolation.

The single architectural fact worth understanding properly, because most of HANA's behaviour follows from it.

A row store keeps a record's fields together. Reading one whole record is one read, which suits transactional work. Summing one column across a million records means touching every field of every record.

A column store keeps each column together. Summing one column reads only that column, which is why aggregation is fast. Reading one whole record means assembling it from every column, which is slower than a row store would be.

Two consequences follow. Compression is far better, because a column holds values of one type with many repeats, and dictionary encoding replaces them with small integers. Compression is not incidental: it is what makes keeping the data in memory affordable.

And selecting fewer columns genuinely costs less, in a way it did not on a row database. SELECT * on a wide table reads every column; selecting three reads three. That is why the guidance to name your fields is stronger here than it used to be.

Small, frequently written tables where whole records are read stay in the row store, which is why both exist rather than one replacing the other.

You interact via SQL and tools like the HANA cockpit, HANA Studio (older) and the database explorer in BAS. For SAP applications, most access is through the ABAP layer (Open SQL/CDS), but modellers and developers also write native SQL/SQLScript and build calculation views.

The practical distinction for anybody coming from ABAP: as an application developer you rarely connect to HANA directly. Open SQL and CDS generate the SQL, and the database is reached through them. Direct SQL is for administration, for analysis and for modelling. Knowing that saves time trying to find where in the application you write a native statement, and it is also the reason HANA SQL is worth learning even if you never type it: it is what your CDS views become.

Half an hour with SQL access to a HANA system, and it makes the theory measurable.

1. Pick a large table and look at its memory consumption in the monitoring views: total size, and the size per column.
2. Note which columns are large. Columns with many distinct values compress least, which is usually free text and identifiers.
3. Run a count with a filter on a well-populated column and note the time.
4. Run the same query selecting every column instead of one. The difference is the column store doing more work.
5. Look at the delta store size for the table. A large unmerged delta on a busy table is the classic performance finding.
6. Check whether the table is column or row store, and consider whether that matches how it is used.

Knowing HANA as a database, its stores, SQL, and pushdown capabilities, lets you model efficiently and exploit its speed (code-to-data) rather than treating it like a slow traditional database.

It also changes what is worth building. Aggregate tables existed because summing was expensive, and on HANA most of them are unnecessary, which is the mechanism behind the simplifications in S/4HANA. Knowing that lets you challenge a design that proposes maintaining a summary table, because the reason that pattern existed has largely gone. See HANA modelling and the database layer.

Modern HANA runs as a multitenant database container system, and the shape is worth knowing because it affects administration and licensing.

A system database handles the overall system: it starts and stops tenants, holds system-wide configuration and does the monitoring. It contains no business data.

Tenant databases each hold one application's data, isolated from each other. An ABAP system connects to one tenant, and another system can run in a second tenant on the same hardware without either seeing the other.

What is shared is the underlying resources: memory, CPU and storage, with limits configurable per tenant so one cannot starve another. What is isolated is data, users and backups.

The practical benefits are consolidation, since several smaller systems can share one machine, and that a tenant can be moved or copied as a unit. The practical risk is the same as any shared resource: a runaway query in one tenant affects the others unless limits are set.

For an administrator the consequence is that there are two levels to look at. A problem may be in the tenant or in the system database, and connecting to the wrong one is the usual first confusion.

Tables grow beyond what one part of the system handles comfortably, and partitioning is the answer, with rules worth knowing before it becomes urgent.

There is a hard technical limit on rows per table partition, in the region of two billion, and a table approaching it must be partitioned or it stops accepting inserts. That is the version of this problem nobody wants to meet unprepared.

Range partitioning splits by a value, typically a date or a fiscal year, which suits data that ages and lets old partitions be moved to cheaper storage.

Hash partitioning spreads rows evenly by a key, which suits distributing load across nodes in a scale-out system.

Round robin distributes without a key, and it gives up the ability to prune.

The benefit beyond capacity is partition pruning: a query filtered on the partitioning column only reads the relevant partitions. A query that does not filter on it reads all of them, which is why the partitioning key should match how the table is actually queried rather than what seemed tidy.

• Treating HANA like a row-store legacy DB.
• Not exploiting pushdown (SQLScript/CDS).
• Ignoring the advanced engines when they fit the problem.

• SELECT * on a wide column store table. It reads every column, and the cost is real in a way it was not before.
• Assuming an index fixes everything. Column stores are searched differently, and indexes are useful in narrower circumstances than on a row database.
• Ignoring the delta store. See HANA backup and calculation views for the operational and modelling layers.

• Letting a table approach the partition row limit unwatched. It is a hard limit, and meeting it unprepared stops inserts on a live system.

Reading a table is straightforward, and understanding what the store, the compression and the delta merge are doing to your query is the part you do in the course.

The habit worth carrying from this page is naming your columns. On a column store, selecting three fields instead of forty is not tidiness, it is the difference in how much data is read.