Free Web Development Online Tutorials, Learn to Code
Delete and Truncate SQL Example, Difference between Delete and Truncate in SQL Database

SQL Delete and Truncate Statement Example


Difference between Truncate and Delete

Here we learn the difference between Delete and Truncate in SQL

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

sql delete statement example

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 in SQL

  • 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

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

sql truncate statement example

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.

Comment
Name
Email
Website
Subscribe