Database Reference
In-Depth Information
As you see in Figure 4-4 , the execution plan of the second UPDATE statement requires SQL Server to update data
in both clustered and nonclustered indexes.
Figure 4-4. Execution plan with covering index
That behavior reduces the performance of data modification queries, introducing additional locking in the
system and contributing to index fragmentation. You need to be careful and consider the pros and cons of making an
index covering on case-by-case basis.
Note
We will discuss locking in detail in part 3, “locking, Blocking, and Concurrency.”
It is important to choose when to add a column to the index key or make it an included column. While, in both
cases the column is present on the leaf-level of the index, included columns are not SARGable. Let's compare two
indexes, as shown in Listing 4-4.
Listing 4-4. Included vs. key columns: Index creation
drop index IDX_Customers_LastName_FirstName_PhoneIncluded on dbo.Customers;
drop index IDX_Customers_LastName_FirstName on dbo.Customers;
create index IDX_Key on dbo.Customers(LastName, FirstName);
create index IDX_Include on dbo.Customers(LastName) include(FirstName);
The data in the IDX_Key index is sorted based on LastName and then on FirstName . The data in IDX_Include is
sorted based on LastName only. FirstName does not affect the sorting order in the index at all.
 
 
Search WWH ::




Custom Search