Difference between Delete and Truncate

Delete

Removes the records and remember the last primary key of the removed records.

This will allow the next records that will be inserted to start from the next number of the last deleted record.

For instance, if you create a table tblStudent with two columns:

Step 1

Create table tblStudent

(StudentNumber int Constraint PK_tblStudent PRIMARY KEY IDENTITY(1,1),

StudentName nvarchar(20))

StudentNumber - that has an int type, a constraint PK_tblStudent  and serves as the Primary Key with identity(1,1). The identity(1, 1) helps you to incrementally assign primary key starting from 1 without manually inserting those.

StudentName - that has just an nvarchar(20)

Step 2

Then use the insert  to insert into tblStudent values ('Maxy'), ('Byte')

once inserted it shows a table like this:

Step 3

Then use the delete to delete from tblStudent, then the two records will be deleted

as you can see below:

Because you have deleted the record using delete, now when inserting new records the last primary key number deleted was taken into remembrance and new records studentNumber start from the next number of the previously deleted number. To see that proceed to step 4. Note, this isn't the same when the records are truncated.

Step 4

Insert again using the insert into tblStudent values ('Maxy'), ('Byte').

This time what do you expect to see at StudentNumber column, 1 and 2 ? no that is wrong, it will come as 3 and 4.

                                           

If  I had used truncate to remove the records intially before inserting a new one then what would have happened? will it still come as 3 and 4 or 1 and 2 next time? 

Yes it will come 1 and 2. 

What is Truncate then?

TRUNCATE

Removes the records but never remember the last primary key of the removed records. This will allow the next records that will be inserted to start from the beginning again.

 

To verify this, instead of using delete at Step 3, do the same for truncate , with command 

truncate table tblStudent

Then repeat Step 4 to insert a new record, once inserted,  It shows a table where the StudentNumber start from the beginning and not from 3 down to 4 but from 1 down to 2: