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