SQL Delete and Truncate Statement Example

SQL delete and truncate statement does almost same job, delete records from sql table, in this tutorial, you will learn how to write SQL delete statement and SQL truncate statement

Here we learn the difference between Delete and Truncate in SQL, When to use delete statement and when to use truncate, and what would be the impact on sql database.

// delete all the data
delete  from tbTeacher                

// delete all the data and reset index
truncate table tableName

Delete statement in SQL

Delete statement will delete data from Table, delete statement can be written with where clause.

Let's take a look at the SQL Delete example

delete  from tbTeacher                
//or you can write               
delete from tbTeacher where teacherId = 1 

// Write some insert statement 
insert into tbTeacher 
(FullName, Address, Contactnumber, Updatedon)
Values
('Some Name','Some Address', '9000000', getdate())


// Select * from tbTeacher

Notice in above screenshot: every time you execute a delete statement on a table and then keep inserting new records again and again, the index value will be automatically incremented for new records

• Truncate statement will delete all data from Table
• Truncate statement reset the identity column value with seed value
• Truncate statement cannot be written with where clause
• if you have any FOREIGN KEY Referenced, then it will not allow to truncate the table and throw error "Cannot truncate table 'tableName' because it is being referenced by a FOREIGN KEY constraint".

Let's take a look at the SQL Truncate example, notice you cannot write where clause with truncate query.

Truncate table tbTeacher
                
//you cannot  write where clause with Truncate
                
Truncate table tbTeacher where teacherId = 1 

Notice in above screenshot: every time you execute a truncate statement on a table and then keep inserting new records again and again, the index value will NOT be incremented for new records, it will always starts from one.

NOTE: Like Delete Statement you can NOT use WHERE clause with Truncate statement.