SQL Server DELETE

SQL Delete statement is used to delete records from table based on specific conditions which are specified in where clause.

• It is a DML command.
• It deletes the records based on conditions specified in the WHERE clause , and removes all the records if there is no Where clause. Condition given in where clause can be any logical, comparison operator as  >= ,<=,= , <> , > ,< , and like and so on..
• Identity column in a table is not reset when records are deleted from a table.
• It removes records one at a time and keeps an entry in the transaction log for each deleted row.
• It supports the Triggers.
• Delete statement can be rolled back.

 

SYNTAX

DELETE FROM table_name WHERE CONDITION..

Lets look at an example of Delete statement in SQL  SERVER.

First we Create a table named as SALES and insert some records in table.

CREATE TABLE dbo.SALES
(SALES_ID INT IDENTITY(1,1)  , 
ITEM VARCHAR(50), 
ITEM_CODE VARCHAR(15),
ITEM_QTY INT,
PURCHASED_DATE DATE
)

INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES( 'LAPTOP- HP','PAVILLION-XCV89', 500,'2019-04-05'),
('KEYBOARD','KEYB-567', 100,'2019-05-19'),
('MOUSE','MM-ER56', 250,'2019-05-15'),
('PEN DRIVE','PD-20ET', 400,'2019-07-25'),
('RADEON GRAPHIC CARD',null, 478,'2019-09-19') 

Now we have a sales table and it contains five records as shown below.

SELECT * FROM dbo.SALES

Delete specific records from a table

Following delete statement deletes a record from table Sales for SALES_ID =5.

DELETE FROM dbo.SALES WHERE SALES_ID = 5

Lets check the record in table for sales_Id =5.

As you can see, it returns blank result that means record for SALES_ID =5 is deleted from table.

Delete command with resetting an identity value

Delete command does not reset values for an identity column means an Identity column in a table is not reset when records are deleted from a table.

As you know, in SALES table we have an identity column that is SALES_ID, that you can also check using SP_HELP stored procedure.

SP_HELP SALES

As you can see, SALES_ID have an identity with seed value 1, means value for SALES_ID column is increased by 1 on every new record entry in table SALES.

As you have deleted a record for SALES_ID = 5 from a table SALES recently, now there are four records left in a table, and last Sales_id value is 4.

Lets check the table records to confirm, and you can see there are four records in a table and Max SALES_ID value is 4.

Now, what happens when you insert a new record in table, ideally value for Sales_id column for a new record entry should be 5 as the last Sales_id value for an identity column in a table that we can see in table is 4 so the expected next value should be 5 (4 +1) that is incremented by 1.

Remember you have deleted a record for Sales_id =5 and delete command deletes the records from a table but does not reset a value for an identity column SALES_ID, So when you insert a new record in a table the value for a Sales_id in this case will be be 6 i.e., 5+1 (including the Sales_id value of deleted record as well) .

Lets insert a one record in table.

INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES('LAPTOP- ACER','ACER-ACV89', 400,'2019-07-05')

As you can see, SALES_ID value for new record is 6 , instead of 5. Which means delete command just delete the records from table but does not reset a value of an identity column, does not matter if you deletes all the records from table then insert fresh set of new records, the value for an identity column still will be incremented by 1 to last identity value that table had before deleting a records.

Delete command can be rolled back

Delete command supports roll back , lets look at into through an example.

Execute below scripts , that will delete all records from a table under transaction block, and you see all deleted records are rolled backed immediately after deleting.

BEGIN TRAN
SELECT * FROM dbo.SALES
DELETE FROM dbo.SALES
SELECT * FROM dbo.SALES
ROLLBACK TRAN
SELECT * FROM dbo.SALES

 

As you can see, after deleting a records from table Sales, second select statement returns blank data, but when transaction is rolled back , next select statement returns a data which means deleted records can be rolled back.

What happens if you do not provide Where condition in Delete statement ?

If you do not specify where condition in Delete statement, It will delete all records from table.

 

SELECT * FROM dbo.SALES

You can see, all records has been deleted from table.

 

TRUNCATE VS DELETE

TRUNCATE

DELETE TOP