SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.obj(id INT IDENTITY(1,1), val BIGINT);
 
CREATE CLUSTERED INDEX x ON dbo.obj(val, id);
 
INSERT dbo.obj(val) 
SELECT TOP (300000) CHECKSUM(NEWID()) % 10000
FROM sys.all_columns AS c 
CROSS JOIN sys.all_objects AS o
CROSS JOIN sys.all_objects AS o2
WHERE o.[object_id] > 0
ORDER BY c.[object_id];
300000 rows affected

CREATE TABLE dummy (dummy int)
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON dummy;
SET STATISTICS XML ON
DECLARE @d datetime2 = SYSDATETIME();

DECLARE @Median DECIMAL(12, 2);

DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.obj);
 
SELECT AVG(1.0 * val)
FROM (
    SELECT val FROM dbo.obj
     ORDER BY val
     OFFSET (@c - 1) / 2 ROWS
     FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;

SELECT DATEDIFF(mcs, @d, SYSDATETIME());

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="DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.obj)" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x0900CEE6E42764F72F9665A4B1B0CD6026840000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="1.47476" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xCB46B0E6BF8E8D6F" QueryPlanHash="0x5B379C58211FFA87" 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="216"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="426608"></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="49" CpuTime="49"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="1.47476" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[Expr1003],0)"><Convert DataType="bigint" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="11" EstimatedTotalSubtreeCost="1.47476" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.18" AvgRowSize="11" EstimatedTotalSubtreeCost="1.47476" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="49" ActualCPUms="49" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="300000" EstimatedRowsRead="300000" EstimateIO="0.964606" EstimateCPU="0.330157" AvgRowSize="9" EstimatedTotalSubtreeCost="1.29476" TableCardinality="300000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="300000" Batches="0" ActualElapsedms="49" ActualCPUms="49" ActualScans="1" ActualLogicalReads="1309" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="300000" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Index="[x]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@c" ParameterDataType="bigint" ParameterRuntimeValue="(300000)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

(No column name)

7.500000

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 AVG(1.0 * val)&#xa;FROM (&#xa;    SELECT val FROM dbo.obj&#xa;     ORDER BY val&#xa;     OFFSET (@c - 1) / 2 ROWS&#xa;     FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY&#xa;) AS x" StatementId="2" StatementCompId="3" StatementType="SELECT" StatementSqlHandle="0x09004900FEDA6DCEE922A07BC7958F540EE50000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.00378979" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x3952938BAAB1980E" QueryPlanHash="0x8C71A241BBE13A90" 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="32" CompileTime="0" CompileCPU="0" CompileMemory="216"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="426608"></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="13" CpuTime="13"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="24" EstimatedTotalSubtreeCost="0.00378979" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1007]=(0) THEN NULL ELSE [Expr1008]/CONVERT_IMPLICIT(numeric(19,0),[Expr1007],0) END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Then><Else><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Convert DataType="numeric" Precision="19" Scale="0" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Arithmetic></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="6.05e-05" AvgRowSize="24" EstimatedTotalSubtreeCost="0.00378979" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="13" ActualCPUms="13" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="COUNT_BIG([Expr1006])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="SUM([Expr1006])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Top" LogicalOp="Top" EstimateRows="100" EstimateIO="0" EstimateCPU="1e-05" AvgRowSize="20" EstimatedTotalSubtreeCost="0.00372929" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="13" ActualCPUms="13" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><OffsetExpression><ScalarOperator ScalarString="([@c]-(1))/(2)"><Identifier><ColumnReference Column="ConstExpr1005"><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Column="@c"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></OffsetExpression><TopExpression><ScalarOperator ScalarString="(1)+((1)-[@c]%(2))"><Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="MOD"><ScalarOperator><Identifier><ColumnReference Column="@c"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Arithmetic></ScalarOperator></Arithmetic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></TopExpression><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="100" EstimateRowsWithoutRowGoal="300000" EstimateIO="0" EstimateCPU="0.03" AvgRowSize="28" EstimatedTotalSubtreeCost="0.00371929" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="(1.0)*CONVERT_IMPLICIT(numeric(19,0),[fiddle_c0bad5f61d5043a98f30679abf7da4e4].[dbo].[obj].[val],0)"><Arithmetic Operation="MULT"><ScalarOperator><Const ConstValue="(1.0)"></Const></ScalarOperator><ScalarOperator><Convert DataType="numeric" Precision="19" Scale="0" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="100" EstimateRowsWithoutRowGoal="300000" EstimatedRowsRead="300000" EstimateIO="0.964606" EstimateCPU="0.330157" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00370929" TableCardinality="300000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="150001" Batches="0" ActualElapsedms="13" ActualCPUms="13" ActualScans="1" ActualLogicalReads="659" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="150001" ActualEndOfScans="0" 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_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Index="[x]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></Top></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@c" ParameterDataType="bigint" ParameterRuntimeValue="(300000)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

(No column name)

234476

DECLARE @d datetime2 = SYSDATETIME();

DECLARE @Median DECIMAL(12, 2);

SELECT TOP (1)
  IIF(total % 2 = 0 AND running - count = total / 2, (val + lag) / 2.0, val)
FROM (
    SELECT
      val,
      count = COUNT_BIG(*),
      lag = LAG(val) OVER (ORDER BY val),
      running = SUM(COUNT_BIG(*)) OVER (ORDER BY val ASC ROWS UNBOUNDED PRECEDING),
      total = SUM(COUNT_BIG(*)) OVER ()
    FROM dbo.obj
    GROUP BY val
) a
LEFT JOIN dummy ON 1 = 0
WHERE total / 2 + 1 > running - count
  AND total / 2 + 1 <= running 
OPTION (MIN_GRANT_PERCENT = 5);

SELECT DATEDIFF(mcs, @d, SYSDATETIME());

(No column name)

7.500000

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 TOP (1)&#xa;  IIF(total % 2 = 0 AND running - count = total / 2, (val + lag) / 2.0, val)&#xa;FROM (&#xa;    SELECT&#xa;      val,&#xa;      count = COUNT_BIG(*),&#xa;      lag = LAG(val) OVER (ORDER BY val),&#xa;      running = SUM(COUNT_BIG(*)) OVER (ORDER BY val ASC ROWS UNBOUNDED PRECEDING),&#xa;      total = SUM(COUNT_BIG(*)) OVER ()&#xa;    FROM dbo.obj&#xa;    GROUP BY val&#xa;) a&#xa;LEFT JOIN dummy ON 1 = 0&#xa;WHERE total / 2 + 1 &gt; running - count&#xa;  AND total / 2 + 1 &lt;= running &#xa;OPTION (MIN_GRANT_PERCENT = 5)" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x0900F7511AD6BF00D2B9ECD7208C2A4096180000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.00378201" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xE9B13F163DFD7484" QueryPlanHash="0xB6B8A1DCCC3F6F12" 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="36960" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="906" CompileCPU="453" CompileMemory="608"><Warnings><MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="36960" GrantedMemory="36960" MaxUsedMemory="1152"></MemoryGrantWarning></Warnings><MemoryGrantInfo SerialRequiredMemory="24" SerialDesiredMemory="752" RequiredMemory="24" DesiredMemory="752" RequestedMemory="36960" GrantWaitTime="0" MaxQueryMemory="739288" GrantedMemory="36960" MaxUsedMemory="1152"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1031576"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-03-31T03:49:04.89" ModificationCount="0" SamplingPercent="84.369" Statistics="[_WA_Sys_00000001_35BCFE0A]" Table="[obj]" Schema="[dbo]" Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]"></StatisticsInfo><StatisticsInfo LastUpdate="2023-03-31T03:49:04.36" ModificationCount="0" SamplingPercent="84.369" Statistics="[x]" Table="[obj]" Schema="[dbo]" Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]"></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="62" CpuTime="62"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="20" EstimatedTotalSubtreeCost="0.00378201" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1010"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1010"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1006]%(2)=(0) AND ([Expr1005]-[Expr1003])=[Expr1006]/(2) THEN CONVERT_IMPLICIT(numeric(19,0),[fiddle_c0bad5f61d5043a98f30679abf7da4e4].[dbo].[obj].[val]+[Expr1004],0)/(2.0) ELSE CONVERT_IMPLICIT(numeric(26,6),[fiddle_c0bad5f61d5043a98f30679abf7da4e4].[dbo].[obj].[val],0) END"><IF><Condition><ScalarOperator><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Arithmetic Operation="MOD"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Convert DataType="numeric" Precision="19" Scale="0" Style="0" Implicit="1"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="(2.0)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><Convert DataType="numeric" Precision="26" Scale="6" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="47" EstimatedTotalSubtreeCost="0.00378191" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="62" ActualCPUms="62" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="2" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateRowsWithoutRowGoal="3063.75" EstimateIO="0" EstimateCPU="0.0350533" AvgRowSize="47" EstimatedTotalSubtreeCost="0.00378181" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6.08581" EstimateRowsWithoutRowGoal="18645.4" EstimateIO="0" EstimateCPU="0" AvgRowSize="55" EstimatedTotalSubtreeCost="0.00377037" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10800" Batches="12" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1018]=(0) THEN NULL ELSE [Expr1019] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1018"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="Expr1019"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Window Aggregate" LogicalOp="Window Aggregate" EstimateRows="6.08581" EstimateRowsWithoutRowGoal="18645.4" EstimateIO="0" EstimateCPU="0.00585465" AvgRowSize="55" EstimatedTotalSubtreeCost="0.00377037" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1018"></ColumnReference><ColumnReference Column="Expr1019"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10800" Batches="12" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch"></RunTimeCountersPerThread></RunTimeInformation><WindowAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1018"></ColumnReference><ScalarOperator ScalarString="COUNT_BIG([Expr1003])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1019"></ColumnReference><ScalarOperator ScalarString="SUM([Expr1003])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6.08581" EstimateRowsWithoutRowGoal="18645.4" EstimateIO="0" EstimateCPU="0.000186454" AvgRowSize="39" EstimatedTotalSubtreeCost="0.00376846" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19999" Batches="23" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="Expr1013"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Window Aggregate" LogicalOp="Window Aggregate" EstimateRows="6.08581" EstimateRowsWithoutRowGoal="18645.4" EstimateIO="0" EstimateCPU="0.00585465" AvgRowSize="47" EstimatedTotalSubtreeCost="0.00376839" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19999" Batches="23" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch"></RunTimeCountersPerThread></RunTimeInformation><WindowAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="LAST_VALUE([fiddle_c0bad5f61d5043a98f30679abf7da4e4].[dbo].[obj].[val])"><Aggregate Distinct="0" AggType="LAST_VALUE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Window Aggregate" LogicalOp="Window Aggregate" EstimateRows="6.08581" EstimateRowsWithoutRowGoal="18645.4" EstimateIO="0" EstimateCPU="0.00585465" AvgRowSize="39" EstimatedTotalSubtreeCost="0.00376648" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19999" Batches="23" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch"></RunTimeCountersPerThread></RunTimeInformation><WindowAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="COUNT_BIG([Expr1003])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="SUM([Expr1003])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="6.08581" EstimateRowsWithoutRowGoal="18645.4" EstimateIO="0" EstimateCPU="0.189323" AvgRowSize="23" EstimatedTotalSubtreeCost="0.00376213" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="19999" Batches="23" ActualElapsedms="62" ActualCPUms="62" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></GroupBy><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="97.9192" EstimateRowsWithoutRowGoal="300000" EstimatedRowsRead="300000" EstimateIO="0.964606" EstimateCPU="0.330157" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00370033" TableCardinality="300000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="300000" Batches="0" ActualElapsedms="62" ActualCPUms="62" ActualScans="1" ActualLogicalReads="1309" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="300000" 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_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Column="val"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_c0bad5f61d5043a98f30679abf7da4e4]" Schema="[dbo]" Table="[obj]" Index="[x]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></StreamAggregate></RelOp></WindowAggregate></RelOp></WindowAggregate></RelOp></ComputeScalar></RelOp></WindowAggregate></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="([Expr1006]/(2)+(1))&gt;([Expr1005]-[Expr1003]) AND ([Expr1006]/(2)+(1))&lt;=[Expr1005]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></Top></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

(No column name)

62509