SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Source of rows
DROP TABLE IF EXISTS dbo.S;

CREATE TABLE dbo.S (c1 integer NOT NULL);

INSERT dbo.S (c1)
VALUES (1), (1000), (1000 * 1000);
3 rows affected

DROP TABLE IF EXISTS dbo.I;

-- Destination with identity column
CREATE TABLE dbo.I 
(
    c1 integer IDENTITY NOT NULL PRIMARY KEY
);
-- Test
SET IDENTITY_INSERT dbo.I ON;

DECLARE @Max integer = 1;

SET STATISTICS XML ON;

INSERT dbo.I 
    (c1)
SELECT
    S1.c1
FROM dbo.S AS S1
JOIN dbo.S AS S2
    ON S2.c1 = S1.c1
WHERE
    -- Only 1 row will qualify
    -- Expression prevents implied predicate on table aliased as S1
    S2.c1 + 0 <= 1
OPTION 
(
    --USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'),
    FORCE ORDER -- so S1 goes into the build input
);

SET STATISTICS XML OFF;

SET IDENTITY_INSERT dbo.I OFF;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT dbo.I &#xa;    (c1)&#xa;SELECT&#xa;    S1.c1&#xa;FROM dbo.S AS S1&#xa;JOIN dbo.S AS S2&#xa;    ON S2.c1 = S1.c1&#xa;WHERE&#xa;    -- Only 1 row will qualify&#xa;    -- Expression prevents implied predicate on table aliased as S1&#xa;    S2.c1 + 0 &lt;= 1&#xa;OPTION &#xa;(&#xa;    --USE HINT (&apos;ENABLE_QUERY_OPTIMIZER_HOTFIXES&apos;),&#xa;    FORCE ORDER -- so S1 goes into the build input&#xa;)" StatementId="1" StatementCompId="4" StatementType="INSERT" StatementSqlHandle="0x0900A56E58AE6EB78AEBF7155726B98D63B90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0343669" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x1160862D0CB5F9F6" QueryPlanHash="0x2D3C75155D45A26E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1056" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="16" CompileCPU="16" CompileMemory="288"><MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1056" RequiredMemory="1024" DesiredMemory="1056" RequestedMemory="1056" GrantWaitTime="0" MaxQueryMemory="679416" GrantedMemory="1056" MaxUsedMemory="176"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1195856"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-11-18T14:03:45.02" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000001_35BCFE0A]" Table="[S]" Schema="[dbo]" Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0343669" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[I]" Index="[PK__I__3213663BA6276DDA]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_a18697e4e4a84dbe83cf7c754a548e34].[dbo].[I].[c1] = [Expr1006]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[I]" Column="c1"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0177902" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0243659" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1056" OutputMemoryGrant="1056" UsedMemoryGrant="176" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Hash><DefinedValues></DefinedValues><HashKeysBuild><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S1]" Column="c1"></ColumnReference></HashKeysBuild><HashKeysProbe><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S2]" Column="c1"></ColumnReference></HashKeysProbe><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="3" EstimateIO="0" EstimateCPU="3e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032856" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S1]" Column="c1"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="setidentity([fiddle_a18697e4e4a84dbe83cf7c754a548e34].[dbo].[S].[c1] as [S1].[c1],(917578307),(497),NULL)"><Intrinsic FunctionName="setidentity"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S1]" Column="c1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(917578307)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(497)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S1]" Column="c1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S1]" Column="c1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S1]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="5" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S2]" Column="c1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S2]" Column="c1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S2]" IndexKind="Heap" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="([fiddle_a18697e4e4a84dbe83cf7c754a548e34].[dbo].[S].[c1] as [S2].[c1]+(0))&lt;=(1)"><Compare CompareOp="LE"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_a18697e4e4a84dbe83cf7c754a548e34]" Schema="[dbo]" Table="[S]" Alias="[S2]" Column="c1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></Hash></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


-- Results

-- Only row 1 inserted, value 1
SELECT * FROM dbo.I AS I;

-- Current identity is 1,000,000 (bug!)
SELECT ident_current = IDENT_CURRENT('dbo.I');

-- Add a new row
INSERT dbo.I DEFAULT VALUES;

-- Identity has jumped
SELECT * FROM dbo.I AS I;


c1


1




ident_current


1000000




c1


1

1000001