Update a table faster than an INNER JOIN I have a SQL Server 2012 stored procedure with an inner join that takes 7,387ms to execute (I saw it in Activity Monitor):

[...]
declare @childrenIds as dbo.CodeIdList;

[...]
Update
    dbo.Code
set
    dbo.Code.CommissioningFlag = 21
From 
    dbo.Code
INNER JOIN
    @childrenIds c
ON
    dbo.Code.CodeId =  c.CodeId


CodeIdList is:

CREATE TYPE [dbo].[CodeIdList] AS TABLE (
    [CodeId] INT NULL
);


Is there a faster way to update the Code table than using an INNER JOIN?

I'm not a dba so I don't know if you need more details about my question. If you need them, please ask.

I have run sp_helpindex on Code table with these results:


index_name     | index_description
PK_CODE        | clustered, unique, primary key located on PRIMARY   | CodeId
UC_CODE_SERIAL | nonclustered, unique, unique key located on PRIMARY | Serial


Code table creation script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ...]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)


At this moment there are 1.006.896 rows in the Code table.

Execution plan:



XML version at http://pastebin.com/9yXsRfva

SQL Server version info:


Microsoft SQL Server Management Studio                      11.0.6020.0 Herramientas cliente de Microsoft Analysis Services         11.0.6020.0 Microsoft Data Access Components (MDAC)                      6.1.7601.17514
Microsoft MSXML                                              3.0 4.0 6.0
Microsoft Internet Explorer                                  9.11.9600.18314
Microsoft .NET Framework                                     4.0.30319.42000
Sistema operativo                                            6.1.7601


Select @@VERSION:


Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 6.1  (Build 7601: Service Pack 1)
I have added this index and it has sped up the join to 0ms average duration:

CREATE INDEX [IDX_FLAG_LEVEL_CODE]
     ON Code (CommissioningFlag, AggregationLevelId)
     INCLUDE (Serial, CodeId);


I found the solution by chance, while resolving my previous question.