SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Orders(
orderid INT PRIMARY KEY,
orderdate DATETIME2 INDEX IX_orderdate
)

SET STATISTICS XML ON
/*Seek*/
SELECT orderid, orderdate
FROM dbo.Orders
WHERE orderdate >= '20140101'
AND orderdate < '20150101';

orderid
orderdate

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="SELECT [orderid],[orderdate] FROM [dbo].[Orders] WHERE [orderdate]&gt;=@1 AND [orderdate]&lt;@2" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x09007BD3AE350B99273D404ADE1D8B92AD7A0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x05CC950BF8C43DB1" QueryPlanHash="0x4B6741D9A3FBBD76" 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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="15" CompileCPU="15" CompileMemory="136"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1300136"></OptimizerHardwareDependentProperties><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="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderid"></ColumnReference><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderid"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Index="[IX_orderdate]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(7),[@1],0)"><Identifier><ColumnReference Column="ConstExpr1002"><ScalarOperator><Convert DataType="datetime2" Scale="7" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(7),[@2],0)"><Identifier><ColumnReference Column="ConstExpr1003"><ScalarOperator><Convert DataType="datetime2" Scale="7" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@2"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><ParameterList><ColumnReference Column="@2" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;20150101&apos;" ParameterRuntimeValue="&apos;20150101&apos;"></ColumnReference><ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;20140101&apos;" ParameterRuntimeValue="&apos;20140101&apos;"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

/*Scan*/
SELECT orderid, orderdate
FROM dbo.Orders
WHERE YEAR(orderdate) = 2014;

orderid
orderdate

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="SELECT orderid, orderdate&#xa;FROM dbo.Orders&#xa;WHERE YEAR(orderdate) = 2014" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x0900450882EDD95445066D7B6B781CD907F40000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x689FA421AC2E3662" QueryPlanHash="0xFB6EB59F141E4EDA" 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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="128"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1300136"></OptimizerHardwareDependentProperties><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="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderid"></ColumnReference><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderid"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Index="[IX_orderdate]" IndexKind="NonClustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="datepart(year,[fiddle_36c38b1fb18f4e7e8a98a257acc89007].[dbo].[Orders].[orderdate])=(2014)"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="datepart"><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(2014)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

/*Scan*/
SELECT orderid, orderdate
FROM dbo.Orders
WHERE DATETRUNC(YEAR, orderdate) = '20140101';

orderid
orderdate

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="SELECT orderid, orderdate&#xa;FROM dbo.Orders&#xa;WHERE DATETRUNC(YEAR, orderdate) = &apos;20140101&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x0900A2F927D94EEA197C041FB64055E68BBC0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xAAD97607604625B4" QueryPlanHash="0xFB6EB59F141E4EDA" 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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="128"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1300136"></OptimizerHardwareDependentProperties><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="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderid"></ColumnReference><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderid"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Index="[IX_orderdate]" IndexKind="NonClustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="datetrunc(year,[fiddle_36c38b1fb18f4e7e8a98a257acc89007].[dbo].[Orders].[orderdate])=&apos;2014-01-01 00:00:00.0000000&apos;"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="datetrunc"><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_36c38b1fb18f4e7e8a98a257acc89007]" Schema="[dbo]" Table="[Orders]" Column="orderdate"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2014-01-01 00:00:00.0000000&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>