Creating and Modifying Tables

Objectives

After completing this lesson, you will be able to:

List the SQL datatypes supported by SAP HANA.
Analyze table structure.
Modify Tables.

Introduction

Before data can be inserted into database tables, the tables themselves have to be defined and created.

Some Options to Manage Database Tables in SAP HANA

SAP HANA supports several means of managing tables, including:

Using SQL DDL Statements. This is the only option covered in this course.
Using a form-based editor in the SAP HANA Tools.
Using Core Data Services and the HANA Development Perspective.

Note

SQL and the form-based editor provide no support for managing the lifecycle of database tables. For example, for keeping track of how a table was defined or changed so that changes can be transported to another system without losing data in the target system.

Nevertheless, this course only covers the SQL option. Understanding it can be helpful, for example in instances relating to prototyping or analyzing issues.

Datatypes

When managing database tables, it is important to know the datatype system provided by SAP HANA on the SQL level. These are outlined in the tables below.

The following table lists the numerical SQL datatypes supported by SAP HANA.

Numerical Datatypes

	Data Type	Remark
Integers	TINYINT	unsigned, 8-bit integer values
	SMALLINT	signed, 16-bit integer values
	INTEGER	signed, 32-bit integer values
	BIGINT	signed, 64-bit integer values
Real numbers	DECIMAL(p,s)	Fixed-point decimal number with total number of digits p (precision) and s (scale) digits right of the decimal point (fractional digits).
	SMALLDECIMAL	Floating point number with precision varying dynamically between 1 and 16 and scale between –369 and 368, respectively between 1 and 34 and scale between –6111 and 6167
	DECIMAL
	REAL	32-bit floating point number
	DOUBLE	64-bit floating point number
	FLOAT(N)	32-bit or 64-bit real number with N significant bits. 1 <= N<= 53.

Note

DECIMAL(p,s) is suitable for storing fixed-point decimal numbers with digits in total and s fractional digits, whereas DECIMAL (without precision and scale) is used to store floating point numbers.

Since floating point numbers often cannot be represented exactly in the binary system and are rounded, you should use them with care in database tables. Especially avoid using them in WHERE clauses.

The number 1.0000259 has precision 8 and scale 7. The number 3.1415 requires at least precision 5 and scale 4 to be stored without loss of precision. Using DECIMAL(5,4) it is stored as 3.1415, using DECIMAL(8,7) as 3.1415000.

The following table lists the string-like SQL datatypes supported by SAP HANA.

String/Character Datatypes

Data Type	Remark
VARCHAR(n)	Variable-length ASCII character string with maximum length n (n ≤ 5,000)
NVARCHAR(n)	Variable-length unicode character string with maximum length n (n ≤ 5,000)
ALPHANUM(n)	Variable-length alphanumeric character string with maximum length n (n ≤ 127)
SHORTTEXT(n)	Variable-length unicode character string based on NVARCHAR (n) with support for text- and string-search features

The following table lists the data and time SQL datatypes supported by SAP HANA.

Datatypes for Date and Time

Data Type	Remark
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'

The following table lists the binary and large object SQL datatypes supported by SAP HANA.

Datatypes for Binary Data and Large Objects

	Data Type	Remark
Binary Data	VARBINARY(n)	Binary data, maximum length n Bytes (n ≤ 5,000)
Large Objects	BLOB	Large binary data (maximum 2 GB)
	CLOB	Long ASCII character string (maximum 2 GB)
	NCLOB	Long unicode character string (maximum 2 GB)
	TEXT	Long unicode character string (maximum 2 GB) based on NCLOB with support for text- and string-search features

Datatype VARBINARY can be used to store short values in binary form, for example, UUIDs (Universally Unique IDentifiers):

Code Snippet

1234567
INSERT INTO MyTable 
VALUES ( ... TO_BINARY('Walldorf') ...);
-- Value stored in DB: 57616C6C646F7266

INSERT INTO MyTable 
VALUES ( ... TO_BINARY(x'00075341500700FF') ...);
-- x'..' is a hexadecimal literal value

The term LOB datatype (LOB = large object) is used as a generic term for datatypes such as CLOB (character large object) or BLOB (binary large object).
When using LOB datatypes it is important to note the following:
- LOB columns cannot be part of the primary key.
- LOB columns cannot be used in the ORDER BY clause.
- LOB columns cannot be used in the GROUP BY clause.
- LOB columns may not be part of the JOIN condition (explicit join syntax).
- LOB columns cannot be used as an argument for an aggregate function.
- LOB columns cannot be used in the SELECT DISTINCT clause.
- LOB columns cannot occur in a UNION statement.
- LOB columns cannot be part of a database index.

Create Tables

In ANSI SQL, the statement to create new database tables is CREATE TABLE. SAP HANA supports two types of table stores: row store tables and column store tables. As a result, SAP HANA SQL extends the ANSI syntax to control whether a new table is created in the row store or in the column store. This leads to the following basic syntax:

Basic CREATE TABLE syntax

Code Snippet

123456
CREATE [ROW|COLUMN] TABLE <table_name> (
   <Column Name> <SQL Data Type> [PRIMAY KEY],
   <Column Name> <SQL Data Type> [NULL|NOT NULL],
   <Column Name> <SQL Data Type> [DEFAULT <Default Value>],
   [PRIMARY KEY (<column list>)] [UNIQUE (<column list>)]
)

Row and Column Store Tables

SAP HANA has the ability to create different kinds of tables for different uses; for now, the only distinction that we will focus on is whether the table is row store or column store. The choice of row or column store will affect the structure of the table, the costs to query it, and the sorts of analysis that can be performed against it.

Row store tables are most appropriate when:

The table has a limited number of columns and rows.
The table will not see frequent data change.
All of the columns of the table will be retrieved together.
Aggregation and complex analytics are avoided.

Column store tables are most appropriate when:

The table has a large number of columns and rows.
The table sees frequent data change.
A limited number of columns are retrieved at any one time.
Aggregation and complex analytics are performed.
High storage compression rates are desired.

In general, a table that should have been column store but which was defined as row store is more damaging to performance than a table that should have been row store but which was defined as column store. It is better to assume all tables should be column store unless definite reasons can be presented to the contrary.

Note

You do not have to specify which table store to use by selecting the keywords ROW or COLUMN. The COLUMN store is the default as of SAP HANA 2.0 SPS03, and is the recommended form of table to use in the vast majority of cases. However, it is always best to be explicitly clear (imagine you have found legacy SQL code from before SPS03 that did not specify ROW or COLUMN). This is why the following examples all include the keyword COLUMN.

Create Column Table

Unless quoted, table and column names are treated as all uppercase, regardless of how they are typed.
Case sensitivity requires the use of double quotes.
We recommend leaving table and column names unquoted when creating.

Code Snippet

123456
CREATE COLUMN TABLE "Official"
   ("PNr"	VARCHAR(3),
    "Name"	VARCHAR(20),
    "Overtime"	INTEGER,
    "Salary"	VARCHAR(3),
    "Manager"	VARCHAR(3));

Defining a Table without a Primary Key

You can define a table without a primary key.

If you do not define a key, the table can contain duplicates.

Code Snippet

123456
CREATE COLUMN TABLE Official
   (PNr 	VARCHAR(3), 
    Name 	VARCHAR(20), 
    Overtime 	INTEGER, 
    Salary 	VARCHAR(3), 
    Manager 	VARCHAR(3));

Default Values and Prohibiting NULL Values

You can specify that no NULL values are allowed by adding NOT NULL.
For primary key columns, NULL values are implicitly prohibited.
You can define a default value for a column by adding DEFAULT <value>.
The default value is used on INSERT if no value is specified for the column.
NULL values are not excluded when setting a default value. NOT NULL can also be useful for columns with a default value.
The order of the keywords DEFAULT and NOT NULL is irrelevant.

Code Snippet

12345678
CREATE COLUMN TABLE Official
   (PNr 	VARCHAR(3), 
    Name        VARCHAR(20) NOT NULL, 
    Overtime    INTEGER, 
    Salary      VARCHAR(3) DEFAULT 'A06' NOT NULL, 
    Manager     VARCHAR(3),
    PRIMARY KEY (PNr));

Define A Primary Key

You can define a primary key. If a primary key is defined, the table may not contain duplicates in the key column(s). You can use the primary key definition as part of the column definition, if the primary key consists of only one column.

Code Snippet

123456
CREATE COLUMN TABLE Official
   (PNr 	VARCHAR(3) PRIMARY KEY,
    Name 	VARCHAR(20),
    Overtime    INTEGER, 
    Salary 	VARCHAR(3),
    Manager 	VARCHAR(3));

Primary Key

You must use a separate PRIMARY KEY clause to define a multi-column primary key.

Code Snippet

1234567
CREATE COLUMN TABLE Owner_EU
   (Country 	VARCHAR(3),
    OwnerID 	VARCHAR(3),
    Name 	VARCHAR(20),
    Birthday 	DATE,
    City 	VARCHAR(20),
    PRIMARY KEY (Country, OwnerID));

Unique Constraints

You can specify that column(s) must not contain duplicate values by adding a UNIQUE constraint.
In contrast to the PRIMARY KEY, UNIQUE allows NULL values.
Since NULL values do not equal each other, they are technically unique.
If a UNIQUE column also specifies NOT NULL, it is functionally equivalent to PRIMARY KEY.
Multiple UNIQUE constraints are allowed in one table, but only one PRIMARY KEY (which may be multi-column).

Code Snippet

12345678
CREATE COLUMN TABLE Car
   (CarID 	VARCHAR(3) PRIMARY KEY,
    PlateNumber VARCHAR(10) UNIQUE,
    Brand 	VARCHAR(20),
    Color 	VARCHAR(10),
    HP          INTEGER,
    Owner 	VARCHAR(3));

Multiple Simple Uniqueness Constraints

It is possible to prohibit duplicate values for more than one column.

Using UNIQUE two single-column keys are defined:

Code Snippet

1234567
CREATE COLUMN TABLE Car
   (CarID 	VARCHAR(3) PRIMARY KEY,
    PlateNumber VARCHAR(10),
    Brand 	VARCHAR(20) UNIQUE,
    Color 	VARCHAR(10) UNIQUE,
    HP          INTEGER,
    Owner 	VARCHAR(3));

Composite Uniqueness Constraints

You must use a separate UNIQUE clause to define a multi-column key.

Using UNIQUE a two-column key is defined:

Code Snippet

12345678
CREATE COLUMN TABLE Car
   (CarID 	VARCHAR(3) PRIMARY KEY, 
    PlateNumber VARCHAR(10), 
    Brand 	VARCHAR(20), 
    Color 	VARCHAR(10), 
    HP 		INTEGER, 
    Owner 	VARCHAR(3),
    UNIQUE (Brand, Color));

Modify Tables

Once a table has been defined, you can modify it in certain ways using the ALTER TABLE statement. The statement is always followed by the name of the table to be changed, and one of the following additional keyword to indicate the type of change:

To add one or more columns, use ADD.
To remove a column and its data, use DROP.
To change the properties of columns such as their SQL datatype and default value, use ALTER.

Adding Columns

You can ADD columns to an existing table. You can use NOT NULL only if the table is empty or if you define a DEFAULT value for the added columns. Newly added columns are filled with NULL values or with the corresponding default value.

Code Snippet

123
ALTER TABLE Official
ADD (RemainderDays   INTEGER,
     AnnualLeave     INTEGER NOT NULL DEFAULT 30);

Removing Columns

You can DROP columns from an existing table. You cannot drop columns that are part of the primary key. Also for tables without a primary key at least one column must be left.

Code Snippet

1234
ALTER TABLE Official
DROP (Salary,
      Manager,
      Name);

Changing Column Properties

You can specify a default value for an existing column. The changed default value only affects newly inserted rows, not existing rows. You can allow NULL values for columns for which they were prohibited so far. You can change the datatype of existing columns but type compatibility must be ensured. For example, VARCHAR(20) → VARCHAR(25) is allowed, but not VARCHAR(25) → VARCHAR(20).

Code Snippet

1234
ALTER TABLE Official
ALTER (Overtime INTEGER DEFAULT 7,
       Salary   VARCHAR(5) DEFAULT ꞌA01ꞌ,
       Name     VARCHAR(25) Null);

To remove a default value, use ALTER TABLE ... ALTER and set the default value to NULL.

Removing the Primary Key

You can delete the limitations imposed by the primary key. Only the primary key constraint is deleted, the corresponding columns are not deleted.

Code Snippet

12
ALTER TABLE Official
DROP PRIMARY KEY;

Adding a Primary Key

You can subsequently define a primary key for a table without one. The corresponding columns must not contain NULL values and the uniqueness requirement must not be violated by the existing data.

Code Snippet

12
ALTER TABLE Official
ADD PRIMARY KEY(PNr);

Rename Tables

You can also rename an existing table column. This is not achieved using an ALTER TABLE statement, but using a RENAME COLUMN statement. The respective table can be empty or can contain data. The new column name must be different from the old column name, and may not duplicate an existing column name in the same table.

Column PNr of table Official is renamed into PersNumber:

Code Snippet

1
RENAME COLUMN Official.PNr TO PersNumber;

You can also rename a database table, using a RENAME TABLE statement. The table you want to rename can be empty or can contain data. The new table name must be different from the old table name.

Code Snippet

1
RENAME TABLE Official TO Employee;

Drop Tables

You can also remove an existing table, including its data with the DROP command. No data from the dropped table will be retained, no warning will be issued, and no restoration of the table or its data will be possible following a DROP, so use appropriate care.

Code Snippet

1
DROP TABLE Official;

Next lesson