Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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
add comment

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
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 Gan Jun 4 '13 at 21:17
    
@TravisGan - Thanks, I appreciate it. –  ProfessionalAmateur Jun 4 '13 at 21:24
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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