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

Non-Clustered index updates Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 12:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:59 PM
Points: 415, Visits: 1,584
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.





  Post Attachments 
non-clustered.sqlplan (1 view, 11.70 KB)
TableDef.txt (1 view, 296 bytes)
Post #1363813
Posted Tuesday, September 25, 2012 1:19 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: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
During the execution of the insert/update/delete always. A nonclustered index is never out of date.


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 #1363824
Posted Tuesday, September 25, 2012 4:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:59 PM
Points: 415, Visits: 1,584
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


Post #1363903
Posted Tuesday, September 25, 2012 4:18 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: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 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 #1363911
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse