Sql-server – Nonclustered Index Insert

sql server

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?

Best Answer

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

Related Question