SQL

ms sql truncate table vs delete

-- Quick, no possible rollback
TRUNCATE TABLE my_table;
-- With possible rollback
DELETE FROM my_table;
COMMIT;  -- or ROLLBACK;

delete vs truncate sql server

DELETE:
1.Delete command is used to delete a row in a table.
2.You can rollback data after using delete statement.
3.It is a DML command.
4.It is slower than truncate statement.

-- DELETE Syntax 
DELETE FROM table_name WHERE condition;

TRUNCATE:
1.Truncate is used to delete all the rows from a table
2.You cannot rollback data.
3.It is a DDL command.
4.It is faster.

-- TRUNCATE Syntax 
TRUNCATE TABLE table_name;

truncate delete and drop in sql

DELETE -
-DML COMMAND
-Delete Rows from the table one by one
-We can use where clause with Delete to delete single row
-Delete is slower than truncate
-ROLLBACK is possible with DELETE

DROP-
-DDL COMMAND
-Delete the entire structure or schema
-We can't use where clause with drop
-Drop is slower than DELETE & TRUNCATE
-ROLLBACK IS NOT POSSIBLE WITH DROP

TRUNCATE-
-DDL COMMAND
-Truncate deletes rows at a one goal
-We can't use where clause with Truncate
-Truncate faster than both DELETE & DROP
-Rollback is not possible with Truncate

SQL Delete and Truncate Rows

DELETE FROM Customers
WHERE customer_id = 5;