Do you want to learn about the SAP HANA SQL Database? In this SAP tutorial, you will learn the SAP HANA SQL database and how to use and where to use data types in SAP HANA SQL. In addition to that, you will also learn about multiple data types including Binary, Boolean, character, and Datetime data types will also be discussed briefly.
Data Types in SAP HANA
Maximum all relational database management system (RDBMS) uses SQL as the database language. These are all familiar because it is high-powered, independent, and stable.
A Data type describes attributes of the data value. If there is no value associated with the data type or in the absence of data types, the exceptional value NULL is added in all the data types. A data type describes attributes of the data value.
Data Type Categories
In the SAP HANA database, every data type can be classified by its characteristics as below:
| DATA TYPE NAME | DATA TYPES |
| Datetime types | DATE, TIME, SECONDDATE, TIMESTAMP |
| Numeric types | TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL, SMALL DECIMAL, REAL, DOUBLE |
| Character string types | VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT |
| Boolean Data type | BOOLEAN |
| BINARY | VARBINARY |
| Large object types | BLOB, CLOB, NCLOB, TEXT |
| Multi-valued types | ARRAY |
| Spatial Types | ST_GEOMETRY, ST_POINT |
These are all the Data Type categories in SAP HANA.
SAP HANA Datetime Data Type
The date and time information is stored in the DATETIME data types. Four types of DateTime data types are described below.
| Data Types | Description | Range |
| DATE | It contains a date value in this type. By default, we can represent a data type as a YYYY-MM-DD. where YYYY is the year, MM is the month, and DD is the day. | The range lies between 0001-01-01 and 9999-12-31. |
| TIME | It contains a time value in this data type. The default time value is HH24:MI:SS. | HH24 is represented as an hour from 0-24, MI is minutes from 0-259, and for seconds SS is from 0-59. |
| SECONDDATE | The SECONDDATE data type consists of year, month, day, hour, minute, and second information to represent a date with a time value. | The range lies between 0001-01-01 and 9999-12-31 and HH24 is represented as an hour from 0-24, MI is minutes from 0-259, and for seconds SS is from 0-59. |
| TIMESTAMP | The data type is for the values containing a timestamp. For example, both date and time values can extend up to some digits even after the decimal. The format for timestamp values is YYYY-MM-DD HH24:MI: SS: F7 by default. FF is the fraction value and it can be set by FF<n>. | The range lies between 0001-01-01 00:00:00.00000 and 9999-12-31 23:59:59.99999. |
This is what the DATETIME datatype is in SAP HANA.
Numeric Data type
The numeric data types are declared for storing numeric data in several ways. Each data type has a minimum to a maximum value. But if less than a minimum value or greater than a maximum value is not supported. The different types of Numeric data type are mentioned below.
| Data Types | Description | Range |
| TINYINT | This data type stores an 8-bit unsigned integer | The range lies between a minimum of 0 and a maximum of 255. |
| SMALLINT | This stores a data type of a 16-bit signed integer. | The value lies between minimum -32768 and maximum 32767. |
| INTEGER | This stores a data type of a 32-bit signed integer. | The range for integers lies between -2,147,483,648 and 2,147,483,648. |
| BIGINT | This integer stores a 64-bit signed integer. | The range varies from -9,223,372.036,854,775,808 to 9,223,372.036,854,775,808 |
| DECIMAL | In this, we can use (<p> <s>) for values stored in fixed point decimals. p – precision, describes the total number of digits(whole plus fractional digits) s – scale, which describes the number of fractional digits. | The range for precision value is 1-38 whereas the scale is 0 to a value of <p>. For example, decimal(5,4) is determined, then values 3.14, 3.1415, and 3.141592 are reserved in 3.1400, 3.1415, keeping particular precision(5) and scale(4). |
| SMALL DECIMAL | This data type is a floating point decimal number. | The precision varies from 1-16 and -369 to 368 for scale. This data type supports only by the column store. |
| REAL | Stores the value of a 32-bit floating point number. | |
| DOUBLE | This data type stores 64-bit floating points with double precision. | The minimum value to maximum value ranges from -1.7976931348623157E308 to 1.7976931348623157E308. |
These are all the Numeric data types in SAP HANA.
Character String Data Type
The character string data types are declared to store values containing character strings. These are the character string data types: VARCHAR, NVARCHAR, ALPHANUM, and SHORTTEXT. The primary types are VARCHAR and NVARCHAR.
| Data Types | Description | Range |
| VARCHAR | This data type holds the character string of variable length. for the DDL query, we can take n as 1 whereas, in the DML query, we consider n as 5000 because n is the length of the string in characters. | We define the length of the character string by <n> has the range of values 1 and 5000. |
| NVARCHAR | This denotes a character set string of variable lengths of Unicode characters. If there is no specific value in DDL the n is taken as 1. If there is no particular value in DML, the n is taken as 5000. | The value of <n> can be lies anywhere between 1 and 5000. |
| ALPHANUM | This data type is for alpha-numeric characters. | <n> is the integer ranges between 1 and 127. |
| SHORTTEXT | This data type is used for character strings of variable length. This data type supports text search and string search functions. Supports only column tables and not row tables. |
The above we mentioned all are character string data types in SAP HANA.
Boolean Data Type
This data type is used to store boolean values. The examples of boolean values are true, false, and unknown (NULL). This data type returns 1 for true and 0 for false.
Binary Data Type
This is used to declare the data values that store binary data bytes.
VARBINARY – This data type stores the value of binary data having the length of n bytes. The values lie between 1 and 5000, where n is the integer value. If n is not declared by default we can take it as 1. This data type stores the value of binary data having the length of n bytes. This data type stores the value of binary data having the length of n bytehbgvc
Large Object Data Type (LOB)
Images or text documents in large amounts are stored in large object data types. There are five types of large object data types namely, BLOB, CLOB, NCLOB, TEXT, and BINTEXT.
BLOB – This data type stores a large number of binary data. The values of the BLOB data type can be easily converted into the VARBINARY data type.
CLOB – This data type stores a large amount of 7-bit ASCII characters. Here also we can convert CLOB data type into VARCHAR easily.
NCLOB – This data type has a large number of Unicode character objects. In this, we can easily convert NCLOB into NVARCHAR.
TEXT – It has large text values for the column tables. We can describe only column tables and can not define row tables. It keeps text search features.
BINTEXT – It can store large text values, accompanied by the capability of storing binary data as well. Like the TEXT data type, only columns can be defined but not rows. The BINTEXT column also provides columns of the NCLOB data type.
Multi-Valued Data Type
This data type is used to store a collection of values sharing the same data type. These are also called nested data types. SAP HANA supports all data types in the multi-valued data types like FLOAT, DECIMAL, VARCHAR, and INTEGER. However, it does not support complicated data types like TEXT, LOB, and spatial data types.
ARRAY – This data type stores a collection of values or variables sharing the same data type. Each element in the array type is related to the ordinal position. We can not add, delete, or modify the changes in the element of the array. This also contains NULL value elements.
Spatial Data Type
This spatial data type is for the values that are to occupy spatial data related to spatial objects such as lines or polygons, etc. There are several different types of spatial data types as described below. These particular data types ST_Point and ST_Geometry are only supported in the column tables, not row tables.
- ST_CircularString
- ST_GeometryCollection
- ST_LineString
- ST_MultiLineString
- ST_MultiPoint
- ST_MultiPolygon
- ST_Point
- ST_Polygon
Conclusion
Hope this article helps you to go through the SAP HANA SQL Database. Here I explained all the data types present in SAP HANA. Go through the points and have clarity in data types. Below are the topics covered in SAP HANA.
- Data type in SAP HANA
- Data type categories
- SAP HANA Datetime Data type
- Numeric Data type in SAP HANA
- Character String Data type
- Boolean Data type
- Binary Data type
- Spatial Data type
