July 17, 2012 at 11:38 pm
It is the clustered index key that stored in the nonclustered leaf level pages and not the address of any thing unless the tables is a heap which in that case instead RID (pointer to actual data) is stored. During the rebuild process the clustered pages are changed but as I said before in case of needing to read the data sql server uses the clustered key and then searches in the clustered index.index creation is something else becasue if you first create noncluster index it stores RID in the leaf pages and if you create cluster index afterward the noncluster index stores the cluster key instead of RID . That is why the nonecluster index is rebuilt in that case. Same is true with droping the cluster index in the latter Cluster key is replaced by RID in the leaf pages of noneclustered index and it has to be rebuilt.
Pooyan
July 17, 2012 at 11:44 pm
pooyan_pdm (7/17/2012)
It is the clustered index key that stored in the nonclustered leaf level pages and not the address of any thing unless the tables is a heap which in that case instead RID (pointer to actual data) is stored. During the rebuild process the clustered pages are changed but as I said before in case of needing to read the data sql server uses the clustered key and then searches in the clustered index.index creation is something else becasue if you first create noncluster index it stores RID in the leaf pages and if you create cluster index afterward the noncluster index stores the cluster key instead of RID . That is why the nonecluster index is rebuilt in that case. Same is true with droping the cluster index in the latter Cluster key is replaced by RID in the leaf pages of noneclustered index and it has to be rebuilt.
Rebuilding the clustered index can cause data movement at the page level. This will result in the rebuilding of the nonclustered indexes as they will nolonger point to the data pages in the clustered index. They will need to be updated as the data moves between pages.
July 17, 2012 at 11:57 pm
They don't point to anything because they are not a pointer it's just the clustered key which is stored in the NCI leaf level pages and this key value remains the same after rebuilding the cluster index .so there is no need to any change in the nonecluster index . You can search in msdn you will change your mind I'm sure
Pooyan
July 18, 2012 at 12:07 am
You can also try it to see what happens
Pooyan
July 18, 2012 at 1:16 am
pooyan_pdm (7/18/2012)
You can also try it to see what happens
I have, I am talking from experience. If you have a clustered index that is highly fragmented, rebuilding it will move data around on the pages putting them in order. This will affect the where the data resides that the nonclustered indexes are pointing to. This results in them being updated as well.
July 18, 2012 at 8:40 am
Check the book SQL SERVER 2008 Administration in action
This is a very complete sql admin reference it says what I'm saying I also checked MSDN and other references on the web . All I saw was the same with my idea.you're free not to accept it!
Pooyan
July 18, 2012 at 1:20 pm
Lynn Pettis (7/18/2012)
pooyan_pdm (7/18/2012)
You can also try it to see what happensI have, I am talking from experience. If you have a clustered index that is highly fragmented, rebuilding it will move data around on the pages putting them in order. This will affect the where the data resides that the nonclustered indexes are pointing to. This results in them being updated as well.
You might be thinking of the RID changes that occur when rebuilding a heap using ALTER TABLE REBUILD. That will most certainly affect all non clustered indexes on the heap. To my knowledge though rebuilding a clustered index has no effect on non clustered index fragmentation because the clustering key will not change during a clustered index rebuild.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2012 at 1:40 pm
opc.three (7/18/2012)
Lynn Pettis (7/18/2012)
pooyan_pdm (7/18/2012)
You can also try it to see what happensI have, I am talking from experience. If you have a clustered index that is highly fragmented, rebuilding it will move data around on the pages putting them in order. This will affect the where the data resides that the nonclustered indexes are pointing to. This results in them being updated as well.
You might be thinking of the RID changes that occur when rebuilding a heap using ALTER TABLE REBUILD. That will most certainly affect all non clustered indexes on the heap. To my knowledge though rebuilding a clustered index has no effect on non clustered index fragmentation because the clustering key will not change during a clustered index rebuild.
Nope. My experiences while using SQL Server 2000 tainted my knowledge. What I found myself having to do there when rebuilding indexes just continued as I moved to SQL Server 2005 and beyond. I have always ensured that when rebuilding (or reorganizing) indexes that I always did the clustered index first then nonclustered indexes.
I have found that my knowledge in this area is antiquated.
July 18, 2012 at 1:49 pm
You're not the only one here with some years of experience here!! I've been doing this for 7 years . can just instead of repeating your claim give a reason IF clustered keys in the noneclustered index are the same before and after the clustered index rebuild proccess, for what reason should the NCI be rebuilt.I repeat my previous posts here :the clustered key in the NCI leaf pages DOES NOT point to any where .it's just a key with a value.
Pooyan
July 18, 2012 at 2:01 pm
pooyan_pdm (7/18/2012)
You're not the only one here with some years of experience here!! I've been doing this for 7 years . can just instead of repeating your claim give a reason IF clustered keys in the noneclustered index are the same before and after the clustered index rebuild proccess, for what reason should the NCI be rebuilt.I repeat my previous posts here :the clustered key in the NCI leaf pages DOES NOT point to any where .it's just a key with a value.
Do not tout years of experience as a means to display authority on a subject...it does not work that way here. Certs are also no means to claim authority, save for maybe the MCM. The point has been made, and everyone is on the same page now which is of great benefit and should have been enough. No need for that type of post pooyan_pdm 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2012 at 2:12 pm
pooyan_pdm (7/18/2012)
You're not the only one here with some years of experience here!! I've been doing this for 7 years . can just instead of repeating your claim give a reason IF clustered keys in the noneclustered index are the same before and after the clustered index rebuild proccess, for what reason should the NCI be rebuilt.I repeat my previous posts here :the clustered key in the NCI leaf pages DOES NOT point to any where .it's just a key with a value.
Your right, I'm wrong. Happy? By the way, I have been working with SQL Server for over 15 years. My comments were based on my experience.
Years of experience doesn't mean anything. I know what I had to do at one point in my career while working with SQL Server 2000 to meet maintenance window requirements and I just continued doing that as I moved on with SQL Server 2005 and beyond.
Anything else you'd like to say to me??
July 18, 2012 at 2:17 pm
I said what i had to
Pooyan
July 18, 2012 at 2:20 pm
pooyan_pdm (7/18/2012)
You're not the only one here with some years of experience here!! I've been doing this for 7 years . can just instead of repeating your claim give a reason IF clustered keys in the noneclustered index are the same before and after the clustered index rebuild proccess, for what reason should the NCI be rebuilt.I repeat my previous posts here :the clustered key in the NCI leaf pages DOES NOT point to any where .it's just a key with a value.
By the way, I went back through my posts and although I mentioned experience it was stated as "In my experience" meaning that this is what happened to me at some point in my career while working with SQL Server.
I never used my years of experience to say what I said was fact. There is a difference in meaning.
July 18, 2012 at 2:21 pm
pooyan_pdm (7/18/2012)
I said what i had to
Really? After I had already said to OPC that I was mistaken? Nope, that looked like gloating to me.
July 18, 2012 at 2:34 pm
To be honest I didn't read the last sentence in your reply to OPC and thought your were saying the exact opposite. Didn't mean to offend you.
Pooyan
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply