SAP HANA - Data Types

You can create row or Column store tables in SAP HANA using create table option. A table can be created by executing a data definition create table statement or using graphical option in HANA studio.

When you create a table, you also need to define attributes inside it.

SQL statement to create a table in HANA Studio SQL Console −

Create column Table TEST (
   ID INTEGER,
   NAME VARCHAR(10),
   PRIMARY KEY (ID)
);

Creating a table in HANA studio using GUI option −

When you create a table, you need to define the names of columns and SQL data types. The Dimension field tells the length of value and the Key option to define it as primary key.

SAP HANA supports the following data types in a table −

SAP HANA supports 7 categories of SQL data types and it depends on the type of data you have to store in a column.

Numeric

Character/ String

Boolean

Date Time

Binary

Large Objects

Multi-Valued

The following table gives the pst of data types in each category −

Date Time

These data types are used to store date and time in a table in HANA database.

DATE − data type consists of year, month and day information to represent a date value in a column. Default format for a Date data type is YYYY-MM-DD.

TIME − data type consists of hours, minutes, and seconds value in a table in HANA database. Default format for Time data type is HH: MI: SS.

SECOND DATE − data type consists of year, month, day, hour, minute, second value in a table in HANA database. Default format for SECONDDATE data type is YYYY-MM-DD HH:MM:SS.

TIMESTAMP − data type consists of date and time information in a table in HANA database. Default format for TIMESTAMP data type is YYYY-MM-DD HH:MM:SS:FFn, where FFn represents fraction of second.

Numeric

TinyINT − stores 8 bit unsigned integer. Min value: 0 and max value: 255

SMALLINT − stores 16 bit signed integer. Min value: -32,768 and max value: 32,767

Integer − stores 32 bit signed integer. Min value: -2,147,483,648 and max value: 2,147,483,648

BIGINT − stores 64 bit signed integer. Min value: -9,223,372,036,854,775,808 and max value: 9,223,372,036,854,775,808

SMALL − Decimal and Decimal: Min value: -10^38 +1 and max value: 10^38 -1

REAL − Min Value:-3.40E + 38 and max value: 3.40E + 38

DOUBLE − stores 64 bit floating point number. Min value: -1.7976931348623157E308 and max value: 1.7976931348623157E308

Boolean

Boolean data types stores Boolean value, which are TRUE, FALSE

Character

Varchar − maximum of 8000 characters.

Nvarchar − maximum length of 4000 characters

ALPHANUM − stores alphanumeric characters. Value for an integer is between 1 to 127.

SHORTTEXT − stores variable length character string which supports text search features and string search features.

Binary

Binary types are used to store bytes of binary data.

VARBINARY − stores binary data in bytes. Max integer length is between 1 and 5000.

Large Objects

LARGEOBJECTS are used to store a large amount of data such as text documents and images.

NCLOB − stores large UNICODE character object.

BLOB − stores large amount of Binary data.

CLOB − stores large amount of ASCII character data.

TEXT − it enables text search features. This data type can be defined for only column tables and not for row store tables.

BINTEXT − supports text search features but it is possible to insert binary data.

Multivalued

Multivalued data types are used to store collection of values with same data type.

Array

Arrays store collections of value with the same data type. They can also contain null values.