SAP HANA Data Type & Identifiers

Details: Last Updated: 19 October 2020

In this tutorial, we will learn,

1) SAP HANA Data Type

2) SAP HANA Identifiers

SAP HANA Data Type

In SAP HANA Database, SQL Data Type is 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)	4-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. 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	It 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 – 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-