clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811948 fiddles created (25302 in the last week).

-- Creation of Test Data CREATE TABLE #tmp ( ID INT PRIMARY KEY CLUSTERED , WindowStart DATETIME2 , WindowEnd DATETIME2 ) -- Create contiguous data set INSERT INTO #tmp SELECT ID , DATEADD(HOUR, ID, CAST('0001-01-01' AS DATETIME2)) , DATEADD(HOUR, ID + 1, CAST('0001-01-01' AS DATETIME2)) FROM ( SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID --SELECT TOP (87591200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID -- Swap line with above for larger dataset FROM master.sys.configurations t1 CROSS JOIN master.sys.configurations t2 CROSS JOIN master.sys.configurations t3 CROSS JOIN master.sys.configurations t4 CROSS JOIN master.sys.configurations t5 ) x --DELETE 1000000 random records to create random gaps DELETE FROM #tmp WHERE ID IN ( SELECT TOP 1000000 ID --SELECT TOP 77591200 ID -- Swap line with above for larger dataset FROM #tmp ORDER BY NEWID() ) SET STATISTICS XML ON SET STATISTICS IO ON --Desired Output Format - Best Execution I've found so far ;WITH rankIslands AS ( SELECT ID , WindowStart , WindowEnd , ROW_NUMBER() OVER (ORDER BY WindowStart) AS rnk FROM #tmp ), rankGapsJoined AS ( SELECT t1.ID AS NearestIslandID_Lower , t1.WindowEnd AS GapStart_Lower , DATEADD(MINUTE, (DATEDIFF(MINUTE, t1.WindowEnd, t2.WindowStart) / 2), t1.WindowEnd) AS GapEnd_Lower , t2.ID AS NearestIslandID_Higher , DATEADD(MINUTE, -1 * (DATEDIFF(MINUTE, t1.WindowEnd, t2.WindowStart) / 2), t2.WindowStart) AS GapStart_Higher , t2.WindowStart AS GapEnd_Higher FROM rankIslands t1 INNER JOIN rankIslands t2 ON t1.rnk + 1 = t2.rnk AND t1.WindowEnd <> t2.WindowStart ) SELECT xa.* INTO #results FROM rankGapsJoined t1 CROSS APPLY ( VALUES (t1.NearestIslandID_Lower, t1.GapStart_Lower, t1.GapEnd_Lower) ,(t1.NearestIslandID_Higher, t1.GapStart_Higher, t1.GapEnd_Higher) ) AS xa (NearestIslandId, GapStart, GapEnd) ORDER BY 1, 2 SET STATISTICS XML OFF SET STATISTICS IO OFF -- Define Nearest Island for Top 5 gaps (e.g. 2 records per gap) SELECT TOP 10 * FROM #results ORDER BY 1 DESC, 2 DESC DROP TABLE #results DROP TABLE #tmp
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH rankIslands AS (&#xd;&#xa;&#x9;SELECT&#x9; ID&#xd;&#xa;&#x9;&#x9;&#x9;, WindowStart&#xd;&#xa;&#x9;&#x9;&#x9;, WindowEnd&#xd;&#xa;&#x9;&#x9;&#x9;, ROW_NUMBER() OVER (ORDER BY WindowStart) AS rnk&#xd;&#xa;&#x9;FROM&#x9;#tmp&#xd;&#xa;), rankGapsJoined AS (&#xd;&#xa;&#x9;SELECT&#x9; t1.ID AS NearestIslandID_Lower&#xd;&#xa;&#x9;&#x9;&#x9;, t1.WindowEnd AS GapStart_Lower&#xd;&#xa;&#x9;&#x9;&#x9;, DATEADD(MINUTE, (DATEDIFF(MINUTE, t1.WindowEnd, t2.WindowStart) / 2), t1.WindowEnd) AS GapEnd_Lower&#xd;&#xa;&#x9;&#x9;&#x9;, t2.ID AS NearestIslandID_Higher&#xd;&#xa;&#x9;&#x9;&#x9;, DATEADD(MINUTE, -1 * (DATEDIFF(MINUTE, t1.WindowEnd, t2.WindowStart) / 2), t2.WindowStart) AS GapStart_Higher&#xd;&#xa;&#x9;&#x9;&#x9;, t2.WindowStart AS GapEnd_Higher&#xd;&#xa;&#x9;FROM rankIslands t1 INNER JOIN rankIslands t2&#xd;&#xa;&#x9;&#x9;ON t1.rnk + 1 = t2.rnk&#xd;&#xa;&#x9;&#x9;&#x9;AND t1.WindowEnd &lt;&gt; t2.WindowStart&#xd;&#xa;)&#xd;&#xa;SELECT&#x9;xa.*&#xd;&#xa;INTO #results&#xd;&#xa;FROM&#x9;rankGapsJoined t1&#xd;&#xa;&#x9;&#x9;CROSS APPLY ( VALUES (t1.NearestIslandID_Lower, t1.GapStart_Lower, t1.GapEnd_Lower)&#xd;&#xa;&#x9;&#x9;&#x9;&#x9;&#x9;&#x9;&#x9;,(t1.NearestIslandID_Higher, t1.GapStart_Higher, t1.GapEnd_Higher) ) AS xa (NearestIslandId, GapStart, GapEnd)&#xd;&#xa;ORDER BY 1, 2" StatementId="1" StatementCompId="6" StatementType="SELECT INTO" RetrievedFromCache="false" StatementSubTreeCost="250165" StatementEstRows="1e+006" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x870F4E05D1C6AC58" QueryPlanHash="0x66369119C11E067B" CardinalityEstimationModelVersion="130"><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="77728" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="88" CompileTime="15" CompileCPU="15" CompileMemory="544"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="77728" RequiredMemory="640" DesiredMemory="77728" RequestedMemory="77728" GrantWaitTime="0" GrantedMemory="77728" MaxUsedMemory="69080" MaxQueryMemory="623792"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1462712"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><WaitStats><Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="15" WaitCount="335"></Wait><Wait WaitType="IO_COMPLETION" WaitTimeMs="125" WaitCount="143"></Wait></WaitStats><QueryTimeStats ElapsedTime="1485" CpuTime="1344"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="1e+006" EstimateIO="22.2728" EstimateCPU="1" AvgRowSize="9" EstimatedTotalSubtreeCost="250165" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="666034" Batches="0" ActualExecutionMode="Row" ActualElapsedms="1453" ActualCPUms="1312" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Table="[#results]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#results].[NearestIslandId] = [Union1011],[#results].[GapStart] = [Union1012],[#results].[GapEnd] = [Union1013]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#results]" Column="NearestIslandId"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Union1011"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#results]" Column="GapStart"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Union1012"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#results]" Column="GapEnd"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Union1013"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1e+006" EstimateIO="0" EstimateCPU="4.18" AvgRowSize="27" EstimatedTotalSubtreeCost="250142" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1011"></ColumnReference><ColumnReference Column="Union1012"></ColumnReference><ColumnReference Column="Union1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="666034" Batches="0" ActualExecutionMode="Row" ActualElapsedms="1062" ActualCPUms="921" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference><ColumnReference Column="Expr1010"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="47" EstimatedTotalSubtreeCost="250137" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference><ColumnReference Column="Expr1010"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="dateadd(minute,datediff(minute,[Expr1015],[Expr1014])/(2),[tempdb].[dbo].[#tmp].[WindowEnd])"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Intrinsic FunctionName="datediff"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1015"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1014"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1010"></ColumnReference><ScalarOperator ScalarString="dateadd(minute, -((1)*(datediff(minute,[Expr1015],[Expr1014])/(2))),[tempdb].[dbo].[#tmp].[WindowStart])"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="MINUS"><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Intrinsic FunctionName="datediff"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1015"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1014"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Arithmetic></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="500000" EstimateIO="15.9831" EstimateCPU="250025" AvgRowSize="51" EstimatedTotalSubtreeCost="250137" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1014"></ColumnReference><ColumnReference Column="Expr1015"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="333017" Batches="0" ActualExecutionMode="Row" ActualElapsedms="1015" ActualCPUms="875" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge ManyToMany="1"><InnerSideJoinColumns><ColumnReference Column="Expr1008"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Column="Expr1016"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="([Expr1005]+(1))=[Expr1008] AND [tempdb].[dbo].[#tmp].[WindowEnd]&lt;&gt;[tempdb].[dbo].[#tmp].[WindowStart]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Residual><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="45" EstimatedTotalSubtreeCost="48.1448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1015"></ColumnReference><ColumnReference Column="Expr1016"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1016"></ColumnReference><ScalarOperator ScalarString="[Expr1005]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="37" EstimatedTotalSubtreeCost="48.0948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1015"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1015"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(datetimeoffset(7),[tempdb].[dbo].[#tmp].[WindowEnd],0)"><Convert DataType="datetimeoffset" Scale="7" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.04" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="296" ActualCPUms="296" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.01" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Segment1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="234" ActualCPUms="234" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1017"></ColumnReference></SegmentColumn><RelOp NodeId="8" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="500000" EstimateIO="0.0112613" EstimateCPU="43.448" AvgRowSize="27" EstimatedTotalSubtreeCost="47.9948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.531652"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="218" ActualCPUms="218" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="1" InputMemoryGrant="77600" OutputMemoryGrant="41112" UsedMemoryGrant="32472"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="500000" EstimatedRowsRead="500000" EstimateIO="3.98543" EstimateCPU="0.550079" AvgRowSize="27" EstimatedTotalSubtreeCost="4.5355" TableCardinality="500000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="5397" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="500000" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Index="[PK__#tmp______3214EC27EDA73F49]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="37" EstimatedTotalSubtreeCost="48.0948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Expr1014"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1014"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(datetimeoffset(7),[tempdb].[dbo].[#tmp].[WindowStart],0)"><Convert DataType="datetimeoffset" Scale="7" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.04" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="406" ActualCPUms="265" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.01" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Segment1018"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="375" ActualCPUms="234" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1018"></ColumnReference></SegmentColumn><RelOp NodeId="13" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="500000" EstimateIO="0.0112613" EstimateCPU="43.448" AvgRowSize="19" EstimatedTotalSubtreeCost="47.9948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OutputList><Warnings><SpillToTempDb SpillLevel="1" SpilledThreadCount="1"></SpillToTempDb><SortSpillDetails GrantedMemoryKb="36608" UsedMemoryKb="36608" WritesToTempDb="1175" ReadsFromTempDb="1175"></SortSpillDetails></Warnings><MemoryFractions Input="0.468348" Output="0.468348"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="359" ActualCPUms="218" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="36608" OutputMemoryGrant="36224" UsedMemoryGrant="36608"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="14" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="500000" EstimatedRowsRead="500000" EstimateIO="3.98543" EstimateCPU="0.550079" AvgRowSize="19" EstimatedTotalSubtreeCost="4.5355" TableCardinality="500000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="31" ActualCPUms="31" ActualScans="1" ActualLogicalReads="5397" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="500000" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Index="[PK__#tmp______3214EC27EDA73F49]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Merge></RelOp></ComputeScalar></RelOp><RelOp NodeId="15" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="2" EstimateIO="0" EstimateCPU="2.157e-006" AvgRowSize="27" EstimatedTotalSubtreeCost="1" Parallel="0" EstimateRebinds="499999" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1011"></ColumnReference><ColumnReference Column="Union1012"></ColumnReference><ColumnReference Column="Union1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="666034" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="333017" ActualExecutions="333017"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[ID]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[WindowEnd]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[Expr1009]"><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Row><Row><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[ID]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[Expr1010]"><Identifier><ColumnReference Column="Expr1010"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[WindowStart]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Row></Values></ConstantScan></RelOp></NestedLoops></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
NearestIslandId GapStart GapEnd
1499999 13/02/0172 22:00:00 13/02/0172 23:00:00
1499996 13/02/0172 21:00:00 13/02/0172 22:00:00
1499995 13/02/0172 18:30:00 13/02/0172 19:00:00
1499993 13/02/0172 18:00:00 13/02/0172 18:30:00
1499992 13/02/0172 15:30:00 13/02/0172 16:00:00
1499990 13/02/0172 15:00:00 13/02/0172 15:30:00
1499990 13/02/0172 13:00:00 13/02/0172 14:00:00
1499987 13/02/0172 12:00:00 13/02/0172 13:00:00
1499987 13/02/0172 10:30:00 13/02/0172 11:00:00
1499985 13/02/0172 10:00:00 13/02/0172 10:30:00
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 1175, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#tmp________________________________________________________________________________________________________________000000000789'. Scan count 2, logical reads 10794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 hidden batch(es)