SQL Tutorial - TRUNCATE TABLE

Another fantastic SQL Tutorial brought to you by BeardedDev.

You can see all videos on my channel focusing on Business Intelligence and Data Development by clicking the below link:

This tutorial will give you an overview of the TRUNCATE statement as well as discussing the differences compared to DELETE and show examples.

TRUNCATE TABLE removes all data from a table but keeps the table definition, DROP TABLE would completely remove the table.

TRUNCATE TABLE resets the current identity value and that is shown in an example in this video, we use IDENT_CURRENT statement to identify the current value then run the statement a second time after the TRUNCATE TABLE to see the difference.

TRUNCATE TABLE is also minimally logged, meaning only data pages that are removed are logged and we are unable to ROLLBACK. DELETE will log each row and can be rolled back.

We also show an example of the differences in performance between TRUNCATE and DELETE, when I run TRUNCATE it executes almost instantly but DELETE takes 46 seconds.

Don't forget to subscribe and hit the notification button to be made aware of when new videos are uploaded.

SQL Statements used in the video:

SELECT IDENT_CURRENT('dbo.CustomersSTG2') AS Current_Identity

TRUNCATE TABLE dbo.CustomersSTG2

INSERT INTO dbo.CustomersSTG2 (FullName, Age)
SELECT
FullName
, Age
FROM dbo.CustomersSTG

DELETE FROM dbo.CustomersSTG2

-- TRUNCATE SYNTAX
TRUNCATE TABLE [schema].[tablename]

[Dean.unicity]

Great video to see the difference. It's totally clear now

[hope]

i appreciate ur simple explanation, i would like to know the real pronunciation, i am hearing like trunkate(true+nkate) not tra+nkate which is correct

[Gamma]

Gracias amigo, excelente canal. Me suscribo!