Covering Indexes: Not Just for SELECT but also for UPDATE statements
By Mike Byrd
SQL Server covering indexes have gain much attention for performance tuning for SELECT statements, but little has been said on their effect on UPDATE statement. This paper will discuss these factors and show potential performance enhancements for specific instances.
The scripts below will show performance for a covering index for a SELECT statement using the AdventureWorks2017 database and then extend that index to show potential performance benefits for UPDATE statements as well.
Consider the following TSQL statement:
SET STATISTICS IO,TIME ON --poor man's profiler; I use all the time for performance tuning
SELECT SalesOrderID, AccountNumber, CustomerID
WHERE SalesPersonID = 277
ORDER BY 1
SET STATISTICS IO,TIME OFF
-- (473 row(s) affected)
-- query cost = 0.545; 689 logical reads
Type your paragraph here.