Sql-server – Include Columns in Index : SQL Server

index-tuningsql server

I have one question regarding include column in the indexes. If I have an include column which is updating its value quite often, does it update the index every time it's updating the value or does the index only update when key columns are updating? Does include column updates add performance issues? According the example below, if the Balance and DiscountedAmount keep updating does it update the index IX_Temp_RegistrationNumber for every updates?

CREATE TABLE #Temp (
    TheID BIGINT
    ,RegistrationNumber BIGINT
    ,ClientName VARCHAR(50)
    ,Balance NUMERIC(10, 2)
    ,DiscountedAmount NUMERIC(10, 2)
    )

CREATE NONCLUSTERED INDEX [IX_Temp_RegistrationNumber] ON [#Temp] ([RegistrationNumber] ASC) INCLUDE (
    [Balance]
    ,[DiscountedAmount]
    )
GO

Best Answer

Actually if you get Actual Plan, you will be able to see, indexes that modified by every update.

When you do update, Cluster Index and all indexes that keys and included column , had updated, will be modified.

Example 1 :
update #Temp set ClientName = 'foo'
Execution plan : The clustered index will be update

Example 2:
update #Temp set Balance = 'foo'
Execution plan : The clustered index and [IX_Temp_RegistrationNumber] will be update.

Tip :If your table haven't so many indexes or update operations isn't high, then there isn't any performance issue.

Related Solutions

Sql-server – Need to include FKs in SQL Indexed View clustered index

SQL Server will always be able to automatically update the view's index(es) as the source table(s) change; there's nothing extra you need to do.

You can inspect a query plan that modifies one of the source tables, you'll see there are extra operators to update the view's index(es), the same way it has to update all the applicable nonclustered indexes on the source tables when data changes.

SQL Server – Improving Queries Waiting on PAGEIOLATCH_SH

Ideas to get you started:

This is an excellent idea, and the one I'd go for first. Except, SQL Server 2000 does not support INCLUDEd columns in indexes.
Clustered/non-clustered indexes is a different discussion entirely - to put it simply, a clustered index is actually the physical storage "order" of the data (the index is the data), while a non-clustered index stands alone and references the data by pointers. You can think of it as a library, where the clustered index is the actual bookshelves (ordered by type, author, title) and a non-clustered index is just a "rolodex". If you design a non-clustered index so it already contains all the information your query needs (a so-called "covering index"), you won't have to make the lookup-trip to the data, which radically improves query performance.

I would start by looking at why there are explicit index hints.

FROM INVENTTRANS A(INDEX(I_177DIMIDIDX))

.. tells SQL Server to always choose the index I_177DIMIDIDX, which can absolutely kill your query if the index doesn't properly cover all the data you want to retrieve, including the columns INVENTDIMID, DATEPHYSICAL, as well as the huge column list in the SELECT clause and the columns in ORDER BY.

Right off the bat, I would try creating indexes like these to try to resolve the problem.

CREATE INDEX ... ON INVENTTRANS
    (DATAAREAID, ITEMID, INVENTDIMID, DATEPHYSICAL)

A unique index may be even better, if that's possible by adding for instance VOUCHERPHYSICAL. You may still have a problem with the fact that the huge list of selected columns means that the database will have to "join" the index with the (clustered) table to look up those columns, but give it a try.

The second table is simpler, mostly due to the smaller number of search/output columns:

CREATE INDEX ... ON SMI_PL_HEADER
    (DATAAREAID, CUSTACCOUNT, DEADLINEDELIVERY)
    --- INCLUDE (NOMINAL)

Also adding NOMINAL to the indexed columns (you can't INCLUDE it) is certainly not pretty, but if you're desperate, it'll probably help you.

Best Answer

Related Solutions

Sql-server – Need to include FKs in SQL Indexed View clustered index

SQL Server – Improving Queries Waiting on PAGEIOLATCH_SH

Related Question