SQL Basics Part2

hana sql newwData Types available in SAP HANA.

 

 

 

SAP HANA provides the following Data Types in below categories.

  •  Numeric types
  • Character string types
  • Date time types
  • Binary types
  • Large object types

Data types under Numeric types-> TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE                                                                                      

TINYINT 8 bit unsigned 0 to 255
SMALLINT 16 bit signed -32,768 to 32,767
INTEGER 32 bit signed -2,147,483,648 to 2,147,483,647
BIGINT 64 bit signed -9,223,372,036,854,775,808 to  9,223,372,036,854,775,807

DECIMAL(p,s) – The DECIMAL(p, s) data type specifies a fixed-point decimal number with precision p and scale s.The precision is the total number of significant digits.The scale is the number of digits after the decimal point.

SMALLDECIMAL Floating-point decimal number.Precision: variable (1 to 16)    Scale: variable (-369 to 368)
DECIMAL Floating-point decimal number.Precision: variable (1 to 34)   Scale: variable (-6,111 to 6,176 )
REAL Floating-point binary number (32 Bit)
DOUBLE Floating-point binary number (64 Bit)

Data types under Character string types->  VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT                                                                                  

VARCHAR(n) ASCII character string with maximum length n (n ≤ 5,000)
NVARCHAR(n) Unicode character string with maximum length n (n ≤ 5,000)
ALPHANUM(n) Alphanumeric character string with maximum length n (n ≤ 127)
SHORTTEXT(n) Unicode character string with maximum length n (n ≤ 5,000) Special data type supporting text- and string-search features (e. g.Fuzzy Search)

Data types under Date time types->DATE, TIME, SECONDDATE, TIMESTAMP

DATE Consists of year, month, day  ‘2012-05-21’
TIME Consists of hour, minute, second ’18:00:57′
SECONDDATE Combination of data and time ‘2012-05-21 18:00:57’
TIMESTAMP Precision: ten millionth of a second  ‘2012-05-21 18:00:57.1234567’                                        

Data types under Binary types->VARBINARY

VARBINARY(n) Binary data, maximum length n Bytes  (n ≤ 5,000)

Data types under Large object types->BLOB, CLOB, NCLOB, TEXT

CLOB Long ASCII character string (maximum 2 GB)
NCLOB Long Unicode character string (maximum 2 GB)
BLOB Large binary data (maximum 2 GB)
TEXT Long Unicode character string (maximum 2 GB) .Special data type supporting text- and string-search features(e. g.Fuzzy Search) based on NCLOB

When using LOB ( large object), few points to note    

•LOB columns can not be part of the primary key
• LOB columns can not be used in the ORDER BY clause
• LOB columns can not be used in the GROUP BY clause
• LOB columns may not be part of the JOIN condition (explicit JOIN)
• LOB columns can not be used as an argument for an aggregate function
• LOB columns can not be used in the SELECT DISTINCT clause
• LOB columns can not occur in an UNION statement
• LOB columns can not be part of a database index


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s