1
00:00.720 --> 00:01.720
Hello everyone.

2
00:02.520 --> 00:03.560
Good morning all.

3
00:05.480 --> 00:12.800
So this is our first interview question which is related to what is the difference between delete and

4
00:12.840 --> 00:13.640
truncate.

5
00:16.720 --> 00:25.840
So now definition the delete command is SQL removes one or more rows from a table based on the condition

6
00:26.200 --> 00:28.200
specified in those rows.

7
00:31.680 --> 00:39.320
In SQL, truncate command is used to purge a table of all of its rows, regardless of whether or not

8
00:39.320 --> 00:40.920
any conditions are met.

9
00:42.960 --> 00:47.680
Now delete is a DML command, whereas Truncate is a DDL command.

10
00:50.360 --> 00:56.960
It is necessary for us to make a commit manual commit after making changes to the delete command.

11
00:57.560 --> 01:03.640
When you use the truncate command, you your modifications are committed for you automatically, so

12
01:03.640 --> 01:09.440
no need to specify the commit manually while using truncate command.

13
01:10.480 --> 01:15.080
It deletes row one at a time and applies criteria to each deletion.

14
01:16.200 --> 01:19.400
whereas truncate purges all of the rows.

15
01:21.440 --> 01:24.160
At a single time.

16
01:25.080 --> 01:25.600
Right.

17
01:28.720 --> 01:35.880
The Where clause serves as the condition in this case, whereas in truncate the Where clause we cannot

18
01:35.880 --> 01:36.960
define the Where clause.

19
01:36.960 --> 01:37.400
Right?

20
01:40.320 --> 01:42.920
All the rows are locked after deletion.

21
01:43.960 --> 01:50.000
It does this by utilizing table lock, which locks the pages so they cannot be deleted.

22
01:51.760 --> 02:00.120
In comparison to the truncation command, it consumes a greater amount of transaction space it in comparison

23
02:00.120 --> 02:01.800
to the delete command.

24
02:02.160 --> 02:05.200
It makes less use of the transaction space.

25
02:06.680 --> 02:14.440
If there is an identity column, the table identity is not reset to the value it had when the table

26
02:14.440 --> 02:15.480
has created.

27
02:16.400 --> 02:20.760
It returns the table identity to the value it was given as a seed.

28
02:21.800 --> 02:30.800
So regarding identity, the delete command does not reset the identity to its initial state When it

29
02:30.800 --> 02:32.240
was at the beginning.

30
02:33.680 --> 02:36.600
So that is the difference related to identity.

31
02:38.840 --> 02:44.040
Then it requires authorization to delete and it requires table alter permission.

32
02:45.560 --> 02:52.480
When it comes to large database it is much slower and the truncate is much faster than compared to the

33
02:53.280 --> 02:53.960
delete.

34
02:58.280 --> 03:03.360
So this is the main difference between truncate and delete.

35
03:03.880 --> 03:06.680
And one more thing when we are deleting data.

36
03:06.680 --> 03:07.760
So delete from.

37
03:10.320 --> 03:13.480
Table name where we need to specify where condition.

38
03:14.040 --> 03:19.280
So where id greater than one like that.

39
03:22.920 --> 03:28.360
Whereas we cannot specify the where condition into the truncate statement.

40
03:30.000 --> 03:35.520
And here if we do not specify the where condition then it will delete the entire table.

41
03:36.800 --> 03:38.520
So thank you so much everyone.

42
03:38.520 --> 03:42.560
We will come again with the next question as soon as possible.

43
03:43.520 --> 03:44.240
Thank you.