Post reply

What will happen to Non -Clustered Index, when I truncate a table

  • July 16, 2009 at 2:56 am

    #212448

    Hello All,

    I have Job which runs in weekend. This job will Truncate the values in the table and Populate the data again. I have Non-Clustered Index build on some tables. My Questions is ,what will happen to the values in Non-Clustered Index as I am truncating the values.

    After Populating the data back , How does the Index point to the new records.

    Mallik

  • July 16, 2009 at 5:59 am

    #1025547

    As you insert records, the index will get updated. If there's no clustered index on the table, the non clustered index will point to a hidden row id value instead of the clustered index key. Nothing to it.

    BTW, for performance, you might find that dropping the index, truncating & loading the table, and then recreating the index might be faster. It isn't in all cases, but I've found it to be so the majority of the time.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply