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";

Thank You for Visiting My Blog...

Share Your Comments...