August 28, 2009 at 11:17 pm
I used following command to understand the space used by data and index:
SP_SPACEUSED EmpDetails
namerowsreserveddataindex_sizeunused
EmpDetails25 32 KB8 KB24 KB0 KB
When the clustered index is created then the table data is rearranged or table data remains as heap and new organized data (ie clustered key) is created. I am asking that if we have 8KB of data and we create the clustered key then 8K remains same as heap and 24 KB organized data is also created which is called as clustered key.
-LK
August 28, 2009 at 11:25 pm
When a clustered index is created on a heap, the heap is arranged based on the key so the table size should remain more or less same.
In non-clustered index, the keys are stored separately than the heap.
August 28, 2009 at 11:48 pm
No Pradeep, my question is as follows:
When the clustered key is created does the heap still exist?
-Lucky
August 29, 2009 at 2:37 am
No. It doesn't.
August 29, 2009 at 3:09 am
Pradeep you say that heap is cleared off and only clustered index exists as a form of organized data. Now when we drop the clustered index then the index is dropped but the data remains there. Now we should not assume that this is unarranged again.
Anyone can run SP_SPACEUSED and find that this shows both the heap size and clustered index size. I was not very sure so I discussed this. Do you have any doc Pradeep which informs the one you said.
Thanks for participating in discussion.
-LK
August 29, 2009 at 4:40 am
If possible Can U Send us Table stucture with columns and datatypes?
Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉
August 29, 2009 at 5:49 am
when removing a CLIX, the data will get rewritten to an 0 index (=heap).
Check out the structural changes of a heap vs a clustering index, and the effect this has on the rest of the indexes.
BOL: http://msdn.microsoft.com/en-us/library/ms188270%28SQL.90%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2009 at 4:24 am
Thanks ALZDBA,
This was what I was expecting.
-LK
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply