Nonclustered Index Insert

Question

Say I have a table like this:

create table SomeTable
(
    id int identity(1, 1) not null primary key clustered,
    SomeString1 varchar(50) not null,
    SomeString2 varchar(50) not null
)
go

create nonclustered index IX_SomeString1
on SomeTable(SomeString1)
go

If I was to do this:

insert into SomeTable(SomeString1, SomeString2)
values('foo', 'bar')
go

And view the actual execution plan, I only see a Clustered Index Insert. Why am I not seeing a Nonclustered Index Insert in the execution plan?

Answer 1

For a single row insert you get a narrow/per-row plan

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1 type, type
FROM master..spt_values

If you select the Clustered Index Insert Operator and View the properties window you can see the same information as shown in the XML.

If you try for 1,000 rows

INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1000 type, type
FROM master..spt_values

You get a different wide/per-index plan with the operations split out separately

See Wide vs. Narrow Plans or Craig Freedman's blog for more information about the two

Answer 2

Never trust the graphical plan display, is only for newbies. Pros always look at the XML. The NC operation is right there:

<Update DMLRequestSort="false">
  <Object Database="[testdb]" Schema="[dbo]" Table="[SomeTable]" Index="[PK__SomeTabl__3213E83F4AAF1C98]" IndexKind="Clustered" />
  <Object Database="[testdb]" Schema="[dbo]" Table="[SomeTable]" Index="[IX_SomeString1]" IndexKind="NonClustered" />

Answer 3

Because a clustered index defines the physical sort order of the table, when you insert a record you are in fact inserting a record in the clustered index. I don't ever recall seeing non-clustered index inserts/updates in execution plans for inserts/updates, but I have never looked for them either. I tend to focus on poorly performing portion queries that are used to determine which records to insert/update/delete.