SAP HANA SQL Data types – Explained with an example

One of the primary concepts of HANA SQL or any other programming language is the data type. To understand what HANA SQL data types mean, let’s take some examples.

The fruit shop back story

Let’s say you and I work at a fruit shop. I am employee number 1001 and you are employee 1002. We record some sales data for 2 days of our hard work.

Employee Nr.

Product

Date

Quantity Sold(kg)

1001

Apples

1/1/2016

10

1001

Oranges

1/1/2016

5

1002

Apples

1/2/2016

20

1002

Oranges

1/2/2016

10

There are two very important things that we can infer from this data:

You are a better salesman that I am 🙂

Data collected can be of different types. Let’s discuss the different columns that we have here

Product column only has characters from A-Z and no numbers.

Date column has only dates

Employee Nr. and Quantity Sold(kg) have only numbers and no characters

Data types in database terms is a literal translation of itself. The type of data it represents is a data type. By specifying the data type, you tell the database what kind of a value it can expect in that particular field. There a list of SAP HANA data types available which you can refer on this link (SAP documentation links do change frequently so let me know in the comments if it stops working in the future).

HANA SQL Data types – Only the really important ones !!!

More importantly, let me list out the major HANA SQL data types you need to know to work on a real project. These will be enough for most of the scenarios you face. For everything else, there’s that link above.

Data type

Primary Purpose

Example

DATE

Used to represent Date values. Default format is YYYY-MM-DD which can be changed as per requirement

2011-11-20

TIME

Used to represent time. Default format is HH24:MI:SS which stands for Hours in 24hour format:Minutes:Seconds

14:20:56

INTEGER

Used to represent whole numbers within the range -2,147,483,648 to 2,147,483,647

25

DECIMAL

Used to represent numbers with fixed point decimals

25.344689

NVARCHAR

Used to store a character strings 1 to 5000 characters long

abcxyz3h4

Note: NVARCHAR is always the preferred datatype over VARCHAR because it supports unicode character data

So every time, you create a field or a variable (=an object that holds a single data point), you need to tell HANA what kind of data to expect.

Take a look back at our first table on fruit sales and take a guess on what data types you think they might be based on the above information.

I will place space in between to push the answers away . Scroll down for the answers after you have your guess ready.

HANA SQL Data types Knowledge Check: Time for the answers

Product column only has characters from A-Z and no numbers.

It has to be a NVARCHAR as we need to store alphabets.

Date column has only dates

Quite obviously it has to be the DATE data type

Employee Nr. and Quantity Sold(kg) have only numbers and no characters

Now here comes the tricky part. Let’s start with Quantity Sold(kg). It is a number so it can be held by INTEGER, DECIMAL and NVARCHAR (as you can also store numbers as characters). This field contains the number of a particular fruit that you sold which will always be a whole number like 1, 2 or 4 and never like 1.5 or 1.34 (in which case you probably took a bite of the fruit before you tried to sell it to a poor customer). So now we have 2 options – INTEGER and NVARCHAR. Both of them CAN store values for this field but which one SHOULD?

Now ask yourself, will at some point will someone try to add, subtract or do arithmetic calculations on this field. For example, the store manager may try to find the total number of apples sold on a particular day by adding up the individual employee’s sales volume for that day. In our example, the total number of apples sold on day 1 was (10+20 = 30). You can only do these calculations with a numeric data type which is either INTEGER or DECIMAL. Since we have already ruled out DECIMAL, we can infer that INTEGER data type would be the correct option here.

Coming back to the Employee Nr. now, we always have whole numbers as employee IDs so we can safely rule out DECIMAL. Now, we ask the question again will someone want to do any math on this field. Logic dictates that It makes no sense to add or subtract 2 employee IDs and hence we declare it as a NVARCHAR data type so that even if some crazy dude tries to do some math on this field someday, HANA throws an error showing him his logical fallacy.

I hope this was easy to understand and follow. Stay tuned for my further HANA SQL Tutorials for SAP HANA.

Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.

Although I’m from CS background but never seen/come across articulation as sample as your blog, any layman can understood your content easily. Continue your good work and as tiny support from my side I’m sharing each of your blog series in Twitter social media where I am very active!!