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