If I update a column record in a table, will indexes that do NOT have this column in it be affected?

Question

In terms of performance if I have a table like so:

CREATE TABLE [TESTDATA].[TableA](
    [Col1] [nchar](5) NOT NULL,
    [Col2] [nchar](2) NULL,
    [Col3] [float] NULL
CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED 
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And then create a non-clustered index like this:

CREATE NONCLUSTERED INDEX [_idx_TableA]
ON [TESTDATA].[TableA] ([Col2])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

If I perform an update to the table, only changing Col3 does the DB need to touch the index _idx_TableA?

Just curious how far performance touches all indexes?

Answer 1

No, _idx_TableA will not be affected for this operation. I have modified your example and added another index (NCI) that actually includes the key column Col3. Here's my example code:

use testdb;
go

CREATE TABLE [DBO].[TableA](
    [Col1] [nchar](5) NOT NULL,
    [Col2] [nchar](2) NULL,
    [Col3] [int] NULL
CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED 
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_idx_TableA]
ON [DBO].[TableA] ([Col2])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

create nonclustered index IX_Col3
on dbo.TableA(Col3);
go

insert into dbo.TableA
values
    ('a', 'b', 10),
    ('b', 'c', 11),
    ('c', 'd', 12);
go

update dbo.TableA
set Col3 = 13;

If I capture the post execution plan of the UPDATE command, you will see something similar here:

As you see from the above screenshot, the indexes that are updated are the clustered index, and my nonclustered index IX_Col3. The index _idx_TableA does not get updated. Therefore, only the indexes that contain Col3 will be affected, and therefore updated by your particular example.

Answer 2

No. Since the update does not update data in neither col 2 nor the clustered key (col 1), the index _idx_TableA with only col 2 does not get updated.

@professionalAmateur, click on the execution plan button on the top.

Run you query. Execution plan tab should show as below.