SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Non-Clustered index updates


Non-Clustered index updates

Author
Message
SQL*
SQL*
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 1726
Hi All,
Basic question is: When does the non-clustered index update (when we do any updates on table)?

We have created a non-clustered index, when we update the table's column which is the key of non-clustered index then the execution plan is showing only clustered index update.

What about the non-clustered index update?
How the non-clustered index key values modified?


Include Column index:

If we have a non-clustered index by including the column
Ex: TestTable -- id int P.K, Name varchar(50), DeptID INT

i have created non-clustered index on Name by including the DeptID

create index idx_name
on TestTable (Name) Include (Deptid)

that means the non-clustered index leaf level pages contains DeptID also.
My Question is when we update Deptid column of TestTable, how the non-clustered index leaflevel will be updated? Will this non-clustered index update? my execution plan is not showing the non-clustered update.

Please find the attached scripts/execution plan of update statement.

:-)
Attachments
non-clustered.sqlplan (10 views, 11.00 KB)
TableDef.txt (13 views, 296 bytes)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220073 Visits: 46279
During the execution of the insert/update/delete always. A nonclustered index is never out of date.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL*
SQL*
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 1726
That is correct but how it will do?

Say in our case TestTable if we update DeptID of any user then the actual data page of clustered index will be updated first (this is the only actual physical page), and question comes to Non-clustered will the Depid also update at the non-clustered leaf level?

That means two updates at page levels?


Thank you

:-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220073 Visits: 46279
Absolutely. Has to be, otherwise the nonclustered indexes don't match the table (and that's called corruption). Could be more than that if there is an index on that column as the non-leaf levels may need updating too.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search