Hi Friends,
When we update any data which has to modify and update existing non-clustered index using per row is also known as SQL Server narrow plans. In per row operations (narrow plans) the updates to base table and indexes are performed by a single operator and as name suggest one row at a time.
It is possible that SQL Server query optimizer decides to use per-index instead of row however factors like table structure, size and other operations to be carried out by UPDATE statement analysed carefully before choosing the optimal plan. The SQL query below will create a narrow plan.
Note: Click on ‘Display Estimated Execution plan’ if you do not wish to delete data from your sample database or take a backup so that you can revert back the changes.
USE [AdventureWorks2012] DELETE FROM Purchasing.PurchaseOrderDetail WHERE PurchaseOrderDetail.PurchaseOrderDetailID = 850
This delete statement used above will update non-clustered index IX_PurchaseOrderDetail_ProductID as well as clustered index. When number of rows are less, query optimizer is likely to choose a narrow plan. A major drawback of this method is non-clustered index rows are updated using clustered key orders which may hurt performance when number of records are more.
See you soon guys, stay tuned.
Regards,
Kanchan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
