SAP HANA: ALTER statements
To Create Table:
create column table "KABIL_PRACTICE"."DEMO"
(
"ID" integer,
"NAME" Nvarchar(35)
);
To View the data from the table:
select * from "KABIL_PRACTICE"."DEMO";
To alter Column Store to Row Store table:
Alter table "KABIL_PRACTICE"."DEMO" Row;
To alter Row Store to Column Store table:
Alter table "KABIL_PRACTICE"."DEMO" Column;
Alter table to add an additional column.
Alter table "KABIL_PRACTICE"."DEMO" Add ("CITY" Nvarchar(35));
Alter table to change the data type of an existing column.
Alter table "KABIL_PRACTICE"."DEMO" Alter ("CITY" varchar(35));
Note:
For column table, only increasing the size of a column data type is allowed to prevent data loss.
For example, changing from NVARCHAR (20) to NVARCHAR (10) or from INTEGER to TINYINT
raises an error.
For row table, only increasing the size of VARCHAR and NVARCHAR type column is allowed. Other
data type changes are not allowed.
Example:
To add a Salary column:
Alter table "KABIL_PRACTICE"."DEMO" Add ("Salary" Decimal(10,2));
To alter a Salary Column data type:
Alter table "KABIL_PRACTICE"."DEMO" Alter ("Salary" Decimal(15,2));
It raises an error like feature not supported: cannot change the field length:
Salary
To Alter Existing Column with NOT NULL
Alter table "KABIL_PRACTICE"."DEMO" Alter ("ID" integer NOT NULL);
Adding a NOT NULL constraint to an existing column is allowed if either of the following cases
are true:
The table is empty.
The default value is specified when the table contains data.
The table does not contain a NULL-value in that column.
To Add Primary Key for Existing table column
Alter table "KABIL_PRACTICE"."DEMO" Add primary key ("ID");
To Add Primary Key for Multiple Columns in an Existing table
Alter table "KABIL_PRACTICE"."DEMO" Add primary key ("ID","NAME");
To Drop Primary Key for Existing table column
Alter table "KABIL_PRACTICE"."DEMO" drop primary key;
Alter table to Drop column.
Alter table "KABIL_PRACTICE"."DEMO" Drop ("CITY");
COMMENT ON:
This statement is used to add descriptive comments to tables, views or their individual columns in
SAP HANA system.
Comments are a useful way to record a meaningful description of schema tables/views, and their
columns, for future reference.
We can also remove comments using same statement.
Examples:
To create COMMENT ON for DEMO table in
“KABIL_PRACTICE” Schema
COMMENT ON TABLE "KABIL_PRACTICE"."DEMO" IS 'DEMO Table for Alter
Statements';
Note:
we can see this comment by Right click your table from the corresponding schema and
click open definition. A new tab will open from that select Further Properties, you can see
the comment of your table.
To Remove comment on Table
COMMENT ON TABLE "KABIL_PRACTICE"."DEMO" IS null;
To create COMMENT ON ID Column from DEMO table in
“KABIL_PRACTICE” Schema
COMMENT ON COLUMN "KABIL_PRACTICE"."DEMO"."ID" is 'EMPLOYEE_ID';
Note:
we can see this comment by Right click your table from the corresponding schema and
click open definition. A new tab will open with that comment will be shown in the
corresponding column.
To Remove comment on Column
COMMENT ON COLUMN "KABIL_PRACTICE"."DEMO"."ID" is null;
To Rename Column
Rename column "KABIL_PRACTICE"."DEMO"."ID" To "E_ID";
To Rename Table
Rename Table "KABIL_PRACTICE"."DEMO" To "DEMO_TABLE";
SAP RECOMENDED LINK FOR SQL IN HANA
https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/
f327b70cae564c53a766367a8aad0164.html
