Truncate table

  • When we truncate the table does the indexes made on them exists in memory??Can we check whether they exist or no???

  • mehta.saurabhj (3/21/2013)


    When we truncate the table does the indexes made on them exists in memory??Can we check whether they exist or no???

    Not really sure what you mean about indexes being in memory? Truncate will simply deallocate pages from the table. Any indexes are not removed from the table.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • If you are talking about the index definitions, then yes they are still there. Index data is gone - their pages are deallocated as well.

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

    --Pra:-):-)--------------------------------------------------------------------------------

  • tim_harkin (3/21/2013)


    If you are talking about the index definitions, then yes they are still there. Index data is gone - their pages are deallocated as well.

    I mean to say that suppose it i create a non clustered index on column 'name' and then when i truncate the table then will this index remain in memory..or not??? That is will the key and value pair as in case of hash tree will remain or not??

  • mehta.saurabhj (3/21/2013)


    tim_harkin (3/21/2013)


    If you are talking about the index definitions, then yes they are still there. Index data is gone - their pages are deallocated as well.

    I mean to say that suppose it i create a non clustered index on column 'name' and then when i truncate the table then will this index remain in memory..or not??? That is will the key and value pair as in case of hash tree will remain or not??

    Yes the index will remain on the table. I still don't understand what you mean about in memory.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • @sean lange As per my knowledge when you create a non clustered index on a table column they occupy some space in memory as a hash tree or binary tree..So according to this concept when you truncate a table all its data gets removed from table..So i have the doubt that does the table index that i created earlier remains in memory in the form of hash tree or not?

  • mehta.saurabhj (3/21/2013)


    @sean lange As per my knowledge when you create a non clustered index on a table column they occupy some space in memory as a hash tree or binary tree.

    Balanced tree, not hash or binary. Not necessarily in memory any more than the table itself is. The index just consists of index pages in a b-tree structure, the same rules apply to the index pages as to the data pages of the table.

    So according to this concept when you truncate a table all its data gets removed from table..So i have the doubt that does the table index that i created earlier remains in memory in the form of hash tree or not?

    When you truncate a table, all data and index pages are deallocated (on disk and if applicable in memory)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you my doubt is now clear!!!

  • Sean Lange (3/21/2013)


    mehta.saurabhj (3/21/2013)


    tim_harkin (3/21/2013)


    If you are talking about the index definitions, then yes they are still there. Index data is gone - their pages are deallocated as well.

    I mean to say that suppose it i create a non clustered index on column 'name' and then when i truncate the table then will this index remain in memory..or not??? That is will the key and value pair as in case of hash tree will remain or not??

    Yes the index will remain on the table. I still don't understand what you mean about in memory.

    Clustered indexes are stored within the referenced table and not in memory. Memory is volatile so if the indexes were stored there they would have to rebuilt every time the server was started anew!

  • Clustered indexes are stored within the referenced table and not in memory. Memory is volatile so if the indexes were stored there they would have to rebuilt every time the server was started anew!

    Yes I know that I was talking about non clustered indexes and not clustered one!!

  • select * from sys.indexes

    where object_id = (select object_id from sys.objects where name = 'TableName')

    using this query u can check the index that is exist on your table.

    Regards,

    Arjun

  • OK, I didn't read your question properly so sorry about that.

    That said......

    Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions. This information is stored on disk and not in memory so it still makes your question about information stored in memory moot.

    Hope that helps!

  • kevaburg (3/25/2013)


    Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions.

    The view definition is in the system resource database. The data that the view reads is in the system tables of the user database and hence is stored in the specific user database, since views don't store information.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/25/2013)


    kevaburg (3/25/2013)


    Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions.

    The view definition is in the system resource database. The data that the view reads is in the system tables of the user database and hence is stored in the specific user database, since views don't store information.

    Thanks for that Gail......a quick slip of the mind.... 😀

Viewing 15 posts - 1 through 14 (of 14 total)

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