How does using TRUNCATE TABLE affect Indexes

Question

Using SQL Server 2005, upgrading to 2012

If I have an ETL the does the following(Simplified)

TRUNCATE TABLE detination
INSERT INTO detination
SELECT *
FROM source

Does this clear the index and rebuild it with the inserts? Will I have fragments?

"TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement" from http://msdn.microsoft.com/en-us/library/aa260621(v=sql.80).aspx. If you truncate the table, then re-insert data, post-insert it might make the most sense to rebuild the index since you have new data in the table. — Kprof, Dec 28 '12 at 22:27
@Kprof We had apposite situation when weekly DELETE kills server performance until complete index rebuild, but weekly TRUNCATE keep it clean on MSSQL 2005 — alex, Dec 29 '12 at 01:02

usr · Accepted Answer · 2012-12-31T22:12:48.903

Assume it would not truncate the indexes. That would mean the database was physically inconsistent. So it cannot be this way.

Truncate logically removes all rows and physically creates fresh b-trees for all partitions. As the trees are fresh no fragmentation exists.

Actually I'm not sure if the trees have 0 or 1 pages allocated to them. But it doesn't matter. I believe for temp tables there is a special case that has to do with temp table caching. Also doesn't matter.

The insert from your question works the same way as any other insert. It is not influenced by the previous truncate in a cross-statement communication way. Whether it causes fragmentation is dependent on your specific case and, IMHO, best-placed in a new question.

Thanks, I was looking for if it creates the fresh trees – Jmyster Dec 31 '12 at 21:47 — Jmyster, Dec 31 '12 at 21:47

score 7 · Answer 2 · edited May 23 '17 at 10:30

Challenging @sjaan reponse

MSDN "TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain." SQL team is saying that indexes will exist but with no data pages... You could easily check that with reference

If you check the size of indexes on that table it will be zero

SELECT *
FROM
(
    SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
           i.name AS IndexName,
           i.index_id AS IndexID,
           8 * SUM(a.used_pages) AS 'Indexsize(KB)'
    FROM sys.indexes AS i
         JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID
                                     AND p.index_id = i.index_id
         JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
    GROUP BY i.OBJECT_ID,
             i.index_id,
             i.name
) a
WHERE A.TableName LIKE '%table%'
ORDER BY Tablename,
         indexid;

score 2 · Answer 3 · answered Dec 21 '16 at 13:57

2

"TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain."

see article: https://msdn.microsoft.com/en-us/library/ms177570.aspx

answered Dec 21 '16 at 13:57

Sjaan Le Hanie

51
1

Not sure why this was down voted. It is correct and was posted before the @singhswat answer. – Michael Szczepaniak Oct 25 '22 at 21:30

How does using TRUNCATE TABLE affect Indexes

3 Answers3