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?

Answer 1

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.

Answer 2

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;