Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Truncate table Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 9:15 AM
Points: 22, Visits: 33
When we truncate the table does the indexes made on them exists in memory??Can we check whether they exist or no???
Post #1433818
Posted Thursday, March 21, 2013 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433825
Posted Thursday, March 21, 2013 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 246, Visits: 623
If you are talking about the index definitions, then yes they are still there. Index data is gone - their pages are deallocated as well.
Post #1433831
Posted Thursday, March 21, 2013 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, Visits: 271
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain..

--Pra--------------------------------------------------------------------------------
Post #1433834
Posted Thursday, March 21, 2013 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 9:15 AM
Points: 22, Visits: 33
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??
Post #1433850
Posted Thursday, March 21, 2013 9:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433853
Posted Thursday, March 21, 2013 9:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 9:15 AM
Points: 22, Visits: 33
@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?
Post #1433859
Posted Thursday, March 21, 2013 9:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #1433869
Posted Thursday, March 21, 2013 9:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 9:15 AM
Points: 22, Visits: 33
Thank you my doubt is now clear!!!
Post #1433879
Posted Monday, March 25, 2013 7:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 308, Visits: 476
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!
Post #1434922
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse