8 SQL Data Types in SAP HANA – Must check the Sixth one

After learning about SQL operators in SAP HANA, let’s move on to SQL data types in SAP HANA.

Data types assign a characteristic to the data values used in SAP HANA. This tutorial will help you to master in all the different data types used in SAP HANA scripting.

We will start by learning about data types and their uses.

SQL Data Types Used in SAP HANA

There is a wide range of data types that define the data values in a specific way. For instance, if a data value is a number, then it will be of one of the numeric data types like integer, big integer, small integer, float, decimal, etc.

By specifying a data type for the data value, it becomes easier to process the script, understand the specific type and purpose of data values. Data types provide a characteristic to the data values binding everything in a logical frame.

There are eight categories of data types in SQL. We can use all of these data types in SAP HANA:

• Binary Data Type
• Boolean Data Type
• Numeric Data Type
• Characteristic String Data Type
• Datetime Data Type
• Large Object (LOB) Data Type
• Multi-Valued Data Type
• Spatial Data Type

1. Binary Data Type

The binary data type is assigned to the data values that store binary data bytes.

VARBINARY

The VARBINARY(<n>) data type defines the values that store binary data having a length of n bytes.

The value of n can range from 1 to 5000.  ‘n’ is an integer value.

If the value of n i.e. the length of the integer is not specified, then the default is considered as 1.

2. Boolean Data Type

The Boolean data type is assigned to the values to store Boolean values. The Boolean values are TRUE, FALSE and UNKNOWN (also known as NULL). This data type can also return 1 for TRUE and 0 for FALSE if the Boolean type is not supported.

Note: Do not use Boolean type comparisons to compare predicates or use predicates in place of Boolean expressions.

Example of a statement with Boolean values:

SELECT * FROM TABLE1 WHERE
case when ( A>B ) then TRUE when NOT ( A>B ) then FALSE else NULL end=
case when ( C>D ) then TRUE when NOT ( C>D ) then FALSE else NULL end;

3. Numeric Data Type

The numeric data types are assigned to values for storing numeric information in different ways. Each data type has a minimum and maximum value and any value less than the minimum or greater than the maximum is not supported.

The different types of the numeric data type are:

TINYINT

The TINYINT data type store an 8-bit unsigned integer. The range of TINYINT type value is between 0(min) and 255(max).

SMALLINT

The SMALLINT data type store a 16-bit signed integer. The range of TINYINT type value is between -32,768(min) and 32,767(max).

INTEGER

The INTEGER data type store a 32-bit signed integer. The range of TINYINT type value is between -2,147,483,648 (min) and 2,147,483,647 (max).

BIGINT

The BIGINT data type store a 64-bit signed integer. The range of TINYINT type value is between -9,223,372,036,854,775,808 (min) and 9,223,372,036,854,775,807(max).

DECIMAL(precision,scale) or DEC(p,s)

We use the DECIMAL(<p>,<s>) for the values that store fixed-point decimal numbers. <p> stands of precision that defines the total number of digits (the number of whole digits plus fractional digits) and <s> stands for scale defines the number of fractional digits.

The range of precision value is 1 to 38 and that of scale is 0 to the value of <p>.

For instance, if DECIMAL(5,4) is defined, then the values, 3.14, 3.1415, 3.141592 are stored in the column as 3.1400, 3.1415, 3.1415, retaining the specified precision(5) and scale(4).

SMALLDECIMAL

The SMALLDECIMAL data type is for a floating-point decimal number. For these values, the precision and scale vary within the range of 1 to 16 for precision and -369 to 368 for scale. The SMALLDECIMAL values are only supported in the column store.

REAL

We use the REAL data type for the values which store a 32-bit floating-point number with single precision.

DOUBLE

We use the DOUBLE data type for the values which store a 64-bit floating-point number with double-precision. The minimum value is -1.7976931348623157E308 and the maximum value is 1.7976931348623157E308.

FLOAT(n)

The FLOAT(<n>) data type store a 32-bit or 64-bit real numbers. We specify the number of significant bits by <n>. The value of <n> ranges between 1 and 53. If the value of n is below 25 then it is a 32-bit real data type. And if the value of n is more than, equal to or undeclare then the data type is 64-bit.

4. Character String Data Type

The character string data types are assigned to such values that store values containing character strings. There are two main types of character string data types, VARCHAR, and NVARCHAR which we will discuss in detail below.

Only characters of 7-bit ASCII character strings and Unicode character strings are used in these data types:

VARCHAR

The VARCHAR(<n>) data type assigns a character string of variable length. It contains only the 7-bit ASCII character strings.

We indicate the length of the character string by <n> and has a limit of a value between 1 and 5000.

If we use it in a DDL query, then we consider n as 1 by default.

Whereas, if we use it in a DML query then we consider n as 5000 if no specific length is there. In a DML query, n is the length of the string in characters.

NVARCHAR

The NVARCHAR(<n>) defines a character set string of a variable length of Unicode characters. <n> is an integer defining the length of the string in characters. The value of <n> can be anywhere between 1 and 5000.

If no specific value of <n> there in a DDL query then we consider it as 1.

If the value of <n> i.e. no specific length of the string is there in characters in a DML query then we consider it as 5000.

ALPHANUM

The ALPHANUM(<n>) data type is for alpha-numeric characters. The values are of variable length <n>. <n> is an integer ranging between 1 and 127.

SHORTTEXT

The SHORTTEXT(<n>) data type is for character strings of variable-length. The values of this data type support text search and string search features. We can only define the shorttext data type for column tables and not for row tables.

5. Datetime Data Type

The values of datetime data type stores date and time information. There are four types of datetime data type:

DATE

The DATE type is for value which contains a date value. The default format of the date is YYYY-MM-DD. Where YYYY is the year, MM is the month and DD indicates the day. The range of a value having DATE as the data type is between 0001-01-01 and 9999-12-31.

TIME

The TIME data type is for the value containing a time value. The default format for a time value is HH24:MI:SS. Here, HH24 is the hour from 0 to 24, MI is for the minutes from 0 to 59 and SS is for the seconds from 0 to 59.

SECONDDATE

The SECONDDATE data type is for the value which stores both time and date value. That is, it contains a year, month, day, hour, minute and second information. The default format for a seconddate value is YYYY-MM-DD HH24:MI:SS.

Here, HH24 is the hour from 0 to 24, MI is for the minutes from 0 to 59 and SS is for the seconds from 0 to 59. And YYYY is the year, MM is the month and DD indicate the day. The range of the value of this data type is 0001-01-01 00:00:01 and 9999-12-31 24:00:00.

TIMESTAMP

The TIMESTAMP data type is for the values containing a timestamp i.e. both date and time values extend to some digits after the decimal. The default format of timestamp values is YYYY-MM-DD HH24:MI:SS.FF7.

Here, FF is the value in-fraction. You can set the length of fraction values by FF<n>. Thus, the range of values having timestamp data type is between 0001-01-01 00:00:00.0000000 and 9999-12-31 23:59:59.9999999.

6. Large Object (LOB) Data Type

The values of LOB i.e. large object data type contain a large amount of data like images or text documents. There are five main types of LOB data types:

BLOB

The BLOB data type is for values that store large amounts of binary data. We can convert the values of BLOB data type into VARBINARY easily.

CLOB

The CLOB data type is for the values to store a large amount of 7-bit ASCII characters. We can convert the values having CLOB data type into VARCHAR.

NCLOB

The NCLOB data type values contain large Unicode character objects. One can easily convert NCLOB values into NVARCHAR.

TEXT

The TEXT data type values contain large text values for column tables. We can only define it for column tables and not for row tables. This data type yields a column with NCLOB values. Also, it supports text search features.

BINTEXT

The values having BINTEXT data type can store large text values along with being able to store binary data values. Similar to TEXT data type, we can only define it for column tables and not for row tables. The BINTEXT columns also yield columns of NCLOB type.

7. Multi-Valued Data Type

The multi-valued data type is for the value that stores a collection of values sharing the same data type. The multi-valued data type values are also known as nested data types.

SAP HANA supports all basic data types in multi-valued data type values such as FLOAT, DECIMAL, VARCHAR, INTEGER. It does not support the complex ones like TEXT, LOB and spatial data types.

ARRAY

ARRAY data type is for the values that store a collection of values sharing the same data type. Each element in an ARRAY type value must be associated with one ordinal position. You cannot add, remove or change the elements in arrays. The ARRAY type values can also contain NULL values as elements.

8. Spatial Data Type

The spatial data type is for the values which store spatial data regarding spatial objects as points, lines or polygons, etc. There are several different types of spatial data types as given below. The data types ST_Point and ST_Geometry are the only data types that are supported in column tables.

• ST_CircularString
• ST_GeometryCollection
• ST_LineString
• ST_MultiLineString
• ST_MultiPoint
• ST_MultiPolygon
• ST_Point
• ST_Polygon

Data Type Precedence

In the image below, you can check the precedence of data types from highest to lowest.

Summary

These were all the different data types used in SAP HANA. Apply these data types and practice them on SAP HANA for better understanding.

We hope you liked our explanation. In case of any queries or suggestions please drop your comments in the comment box below.