March 21, 2013 at 8:35 am
When we truncate the table does the indexes made on them exists in memory??Can we check whether they exist or no???
March 21, 2013 at 8:41 am
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/
March 21, 2013 at 8:49 am
If you are talking about the index definitions, then yes they are still there. Index data is gone - their pages are deallocated as well.
March 21, 2013 at 8:51 am
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain..
--Pra:-):-)--------------------------------------------------------------------------------
March 21, 2013 at 9:07 am
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??
March 21, 2013 at 9:15 am
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/
March 21, 2013 at 9:20 am
@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?
March 21, 2013 at 9:37 am
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
March 21, 2013 at 9:49 am
Thank you my doubt is now clear!!!
March 25, 2013 at 7:49 am
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!
March 25, 2013 at 8:00 am
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!!
March 25, 2013 at 8:13 am
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
March 25, 2013 at 8:13 am
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!
March 25, 2013 at 9:12 am
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
March 25, 2013 at 9:14 am
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