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:

1. You are a better salesman that I am  🙂 
2. 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

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

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

2. Date column has only dates

Quite obviously it has to be the DATE data type

3. 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.

Stay Motivated. Stay hungry. Until next time. Goodbye.

<<Previous Tutorial                                                                                                                 Next Tutorial>>