Sql-server – Effect of TRUNCATE TABLE different from DELETE on subsequent queries

sql serversql-server-2016truncate

I always thought of TRUNCATE TABLE as an equivalent of DELETE, except that:

1) TRUNCATE can be used only in some cases (no FK referencing table, user must have ALTER permission etc)

2) TRUNCATE is faster to execute

Apart from that, I expected them to have identical results.
But from my experience, it seems that a TRUNCATE improves the performance of subsequent queries over a DELETE.
I have a script that clears some tables, then fills them with millions of rows from another database on the same server, like:

INSERT NEWDB..newtable(a,b,c) SELECT a,b,c FROM OTHERDB..oldtable

Switching the clearing of tables from DELETE to TRUNCATE TABLE made the deletion much faster (as obvious) but also made the subsequent data copying at least 10 times faster.
Interesting to note, the recovery model for the database is SIMPLE, so it is not a matter of logs.
Can someone explain why this is happening? Or maybe I am missing something and the gain in speed is caused by some other factor?

Best Answer

OTHERDB..oldtable --> the data could be in buffers from the first run.

Try running

DBCC DROPCLEANBUFFERS --Not in production though !!

and then your insert query.

Related Solutions

Sql-server – Query to normalize table/combine row text

This should work, I will clean it up later so its more efficient.

DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


;WITH combinedresults(ctid, id, rank, linenumber, combinedtext) 
     AS (SELECT 0, 
                id, 
                rank, 
                linenumber, 
                CAST (sometext AS VARCHAR(8000)) 
         FROM   @Old o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @Old 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT ctid + 1, 
                o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.sometext 
         FROM   @Old o 
                INNER JOIN combinedresults ct 
                  ON ct.id = o.id 
                     AND ct.rank = o.rank 
         WHERE  o.linenumber > ct.linenumber) 

UPDATE n 
SET    combinedtext = ct.combinedtext 
FROM   @New n 
       INNER JOIN (SELECT n.id, 
                          n.rank, 
                          MAX(o.rank) orank 
                   FROM   @new n 
                          INNER JOIN @Old o 
                            ON n.id = o.id 
                               AND o.rank <= n.rank 
                   GROUP  BY n.id, 
                             n.rank) r 
         ON n.id = r.id 
            AND n.rank = r.rank 
       INNER JOIN (SELECT id, 
                          ct.rank, 
                          MAX(ctid) ctid 
                   FROM   combinedresults ct 
                   GROUP  BY ct.id, 
                             ct.rank) r2 
         ON r2.id = r.id 
            AND r2.rank = r.orank 
       INNER JOIN combinedresults ct 
         ON r.id = ct.id 
            AND ct.rank = r.orank 
            AND ct.ctid = r2.ctid 

SELECT * 
FROM   @New

Sql-server – Does TRUNCATE TABLE affects isolation level in Sql Server

TRUNCATE table will take a SCH-M lock on the table.

Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the conflicting SCH-M lock has been released.

Best Answer

Related Solutions

Sql-server – Query to normalize table/combine row text

Sql-server – Does TRUNCATE TABLE affects isolation level in Sql Server

Related Question