SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

TRUNCATE Vs DELETE

     

Q1. What are the difference between TRUNCATE command and DELETE command?

Q2. Can TRUNCATE be rolled back like the DELETE command?

Q3. What are the advantages of TRUNCATE  command compare to DELETE?  

Q1.What are the difference between TRUNCATE command and DELETE command?

TRUNCATE :

1.Remove all pages from the table. You can’t use WHERE clause with TRUNCATE .

2.Deallocate the data pages in the table.

3.Faster than DELETE.

4.Reset the identity column. TRUNCATE  is a DDL statement. So you need ALTER permissions for it.

5.Doesn’t fire trigger.

6.Acquire only page or table lock..

7. TRUNCATE  cannot be used with indexed views.

8. Drop all object’s statistics and leave the table empty. Zero pages are left in the table.

DELETE:

1.Remove all pages from file if used without a WHERE clause. You can use WHERE clause with DELETE.

2.Remove 1 row at a time and store every deleted row in transaction log.

3.Slower than TRUNCATE .

4.For DELETE you need DELETE permission on the table.

5.Fires DELETE triggers.

6.Acquire row/page or table lock.

7. DELETE can be used with indexed views.

8.Keep statistics and allocated space. After executing DELETE statement, still table may contain empty pages.

Q2. Can TRUNCATE be rolled back like the DELETE command?

Myth: TRUNCATE  cannot be rolled back.

Reality: DDL are auto commit. But TRUNCATE  can be rolled back if used inside a transaction block.

Following example demonstrate it:

 

--Create and populate the test table:

Create table dbo.TestTruncate

(

id INT

)

INSERT INTO dbo.TestTruncate(id)

SELECT 1 UNION

SELECT 2 UNION

SELECT 3

BEGIN TRANSACTION--Begin the transaction

      SELECT * FROM dbo.TestTruncate --Check the data availability

     

      TRUNCATE TABLE dbo.TestTruncate --Truncating the table

     

      SELECT

            resource_type,

            request_mode,resource_description,                            

            resource_associated_entity_id

      FROM   sys.dm_tran_locks

--The Database Engine uses schema modification(Sch-M) locks during a table   DDL operation. In request_mode column you can see schema modification (Sch-M) lock request for truncate statement.

               

      SELECT * FROM dbo.TestTruncate --Table will be empty now.

     

ROLLBACK   --Rollback the transaction

SELECT * FROM dbo.TestTruncate--Table will be having all the data.

DROP TABLE dbo.TestTruncate

Q3.What are the advantages of TRUNCATE  command compare to DELETE?

TRUNCATE command has the following advantages compare to DELETE:
-Less number of Locks

DELETE statement locks each row in the table for deletion. TRUNCATE locks the table and page but not each row.

-Less transaction log space.

DELETE statement removes one row at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages and records the page deallocations in the transaction log.