SAP HANA: Difference between TRUNCATE, DELETE and DROP commands

 SAP HANA: Difference between TRUNCATE, DELETE and DROP commands

SQL Delete Statement

The DELETE Statement is used to delete rows from a table.

Syntax of a SQL DELETE Statement

DELETE FROM table_name [WHERE condition];

NOTE:

The WHERE clause in the SQL DELETE command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table are deleted, so be careful while writing a DELETE query without WHERE clause.

After performing a DELETE operation, you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

Example:

To Create a Table:

create column table "KABIL_PRACTICE"."DEMO1"

(

"ID" integer,

"Name" nvarchar(35)

);

To Insert Some Records into the Table:              

insert into "KABIL_PRACTICE"."DEMO1" values (1,'A');

insert into "KABIL_PRACTICE"."DEMO1" values (2,'B');

insert into "KABIL_PRACTICE"."DEMO1" values (3,'C');

insert into "KABIL_PRACTICE"."DEMO1" values (4,'D');

To Delete a Record from the Table:

Delete From "KABIL_PRACTICE"."DEMO1" Where "ID" = 1;

To Delete All the Records from the Table:

Delete From "KABIL_PRACTICE"."DEMO1";

SQL Truncate Statement

·         TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

·         Does not require a WHERE clause, so you cannot filter rows while Truncating

·         Cannot Truncate a table that is referenced by any Foreign Key constraint.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

Example:

To Truncate a Table:

Truncate Table "KABIL_PRACTICE"."DEMO1";

Difference between DELETE and TRUNCATE Statements:

DELETE	TRUNCATE
This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified	This command is used to delete all the rows from the table.
WHERE clause is Optional	There is no WHERE clause
But it does not free the space containing the table.	It frees the space containing the table.

SQL DROP Statement:

·         The SQL DROP command is used to remove an object from the database.

·         If you drop a table, all the rows in the table is deleted and the table structure is removed from the database.

·         Once a table is dropped we cannot get it back, so be careful while using DROP command.

·         When a table is dropped all the references to the table will not be valid.

·         All the tables' rows, indexes, and privileges will also be removed.

·         Cannot drop a table that is referenced by any Foreign Key constraint.

·         No DML triggers will be fired.

·         The operation cannot be rolled back.

Syntax to drop a SQL table structure:

DROP TABLE table_name;

Example:

To Drop a Table:

Drop Table "KABIL_PRACTICE"."DEMO1";

Difference between DROP and TRUNCATE Statement:

DROP	TRUNCATE
If a table is Dropped, Data and the table structure will not exist.	But, if a table is truncated, the table structure remains the same and Data will not exist
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if you want to use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again.	All the relationships with other tables will also be valid.

Thank You for Visiting My Blog...

Share Your Comments...

https://sapguidanceconsultant.blogspot.com/