Nonclustered Index Insert

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?

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 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

Narrow plan

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

Properties Window

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

Wide Plan

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

Solution 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" />

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply