5

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?

  • 1
    Bear in mind that both the below answers are correct, but updates on the underlying tables can force statistics updates on the indexes as well. This is probably less costly than updating the data in the index itself but it still is a cost to consider. – JNK Jun 4 '13 at 20:27
7

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:

enter image description 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.

  • Thanks, how can I find that execution plan myself? That seems extremely handy. – ProfessionalAmateur Jun 4 '13 at 21:08
  • @ProfessionalAmateur Initially you ask the question on my post, I edited the post. – Travis Jun 4 '13 at 21:17
3

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.

enter image description here

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.