SAP HANA Data Types: Numeric, Character String, Decimal

In this SAP HANA Data Types tutorial, we will learn:

1) SAP HANA Data Types – SAP HANA data types include Date Times, Numeric data type, Boolean, Character String, Binary data types, etc.

2) SAP HANA Identifiers – Delimited Identifiers and Undelimited Identifiers

Data Types in SAP HANA

In SAP HANA Database, SQL Data Types are as below:

Classification Sub-Classification SQL Data Type Column Store Type Default Format
Date Times Types Date DATE CS_DAYDATE ‘YYYY-MM-DD’
Time TIME CS_SECONDTIME ‘HH24:MI:SS’
Second Date SECONDDATE CS_LONGDATE ‘YYYY-MM-DD HH24:MI:SS’
Time Stamp TIMESTAMP CS_SECONDDATE ‘YYYY-MM-DD HH24:MI:SS.FFn’
Numeric Types Tiny Integer TINYINT CS_INT 8-bit unsigned integer, Range 0 To 255
Small Integer SMALLINT CS_INT 16-bit signed integer, Range -32,768 To 32,767
Integer INTEGER CS_INT 32-bit signed integer, Range -2,147,483,648 To 2,147,483,647
Big Integer BIGINT CS_FIXED(18,0) 64-bit signed integer, Range -9,223,372,036,854,775,808 To 9,223,372,036,854,775,807
Decimal DECIMAL(p,s)

p-Precision

s- scale

CS_FIXED(p-s,s) Precision p can range from 1 to 38.

The scale s can range from 0 to p for SAP HANA decimal data type.

If precision and scale are not specified, DECIMAL becomes a floating-point decimal number.

Small Decimal SMALLDECIMAL CS_SDFLOAT It is a floating-point decimal number. The precision and scale should be within the range 1~16 for precision and -369~368 for scale, depending on the stored value. SMALLDECIMAL is only supported for column store Table.
Real Number REAL CS_FLOAT single-precision 32-bit floating-point number
Double Number DOUBLE CS_DOUBLE a double-precision 64-bit floating-point number
Float FLOAT(n) CS_DOUBLE It is 32-bit or 64-bit real number.
Where n specifies the number of bits and should be in the range between 1 and 53.
Boolean Boolean BOOLEAN CS_INT TRUE, FALSE And UNKNOWN (NULL).
Character String Variable-Length Character String VARCHAR(n) CS_STRING This HANA string length data type is a Variable-length character string, where ‘n’ specified the maximum length in bytes and this is an integer between 1 and 5000.
Variable-Length Unicode character NVARCHAR(n) CS_STRING Variable-length Unicode character set string, where <n> indicates the maximum length in characters and is an integer between 1 and 5000
Alpha Numeric Character ALPHANUM(n) CS_ALPHANUM Variable length alpha-numeric characters, where n indicates the maximum length and is an integer between 1 and 127
Short Text SHORTTEXT(n) CS_STRING It is Variable-length character string which provide text search and string search features. This data type can be defined for column store tables, but not for row tables.
Binary Types Binary Text VARBINARY(n) CS_RAW Store binary data of a specified maximum length in bytes, where n indicates the maximum length and is an integer between 1 and 5000.
LOB Types(Large Object Types) Binary LOB BLOB CS_RAW Large amounts of binary data
Character LOB CLOB CS_STRING ASCII character data
Unicode Character LOB NCLOB CS_STRING Large Unicode character object
TEXT TEXT CS_STRING The TEXT data type provide text search features. This data type can be defined for column Store tables, but not for row store tables.
BINARY Text Data BINTEXT CS_STRING The BINTEXT data type is similar to data type TEXT and thus supports text search features, but it is possible to insert binary data. This data type can be defined for column tables, but not for row tables.
Multi-valued Types Array ARRAY It stores collections of values of the same data type where each element is related with exactly one position. Arrays can contain NULL values as in the absence of a value.

SAP HANA Identifiers

Identifiers are used to represent name in SQL statement (e.g. table name, view name, column name, index name, synonym name, procedure name, function name, etc.)

There are two types of identifiers in SAP HANA: Delimited identifiers and Undelimited identifiers.

  • Delimited Identifiers – It is enclosed in the delimiter, Double Quotes “”. The identifier can contain any character including special character.
  • Undelimited Identifiers – Undelimited identifiers (table name, column name) must start with a letter and cannot contain any symbols other than a digit or an underscore ‘_’.

There are two types Quotation mark for delimit as below-

  • Single Quotation Mark (‘ ‘) – It is used to delimit the string.
  • Double Quotation Mark (” “)– It is used for delimiting identifiers.

Summary

  • SAP HANA Data Types include: Date Times, Numeric, Boolean, Character String, Binary, etc.
  • SAP HANA Identifiers – Delimited Identifiers and Undelimited Identifiers
  • There are two types Quotation mark for delimit: Single Quotation Mark (‘ ‘) & amp; Double Quotation Mark (” “)