add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name)
Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64)
Jul 25 2020 11:26:55
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
 
2 rows affected

8 rows affected

2 rows affected
AllDates id
2021-06-05 1000
2021-06-06 1000
2021-06-07 1000
2021-06-08 1000
2021-06-09 1000
2021-06-07 1001
2021-06-08 1001
2021-06-09 1001
AllDates id
2021-06-05 1000
2021-06-06 1000
2021-06-07 1000
2021-06-08 1000
2021-06-09 1000
2021-06-07 1001
2021-06-08 1001
2021-06-09 1001 
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
8 1 WITH ListDates(AllDates) AS
( SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates
    WHERE AllDates < @EndDate)

SELECT AllDates, s.id
FROM ListDates as l
 INNER JOIN tv_source as s
    ON l.AllDates >= s.startDate
        AND l.AllDates < DATEADD(dAY,s.noDays,s.startDate)
ORDER BY s.id ASC,l.AllDates ASC
OPTION (MAXRECURSION 0)		 1 1 0 null null null null 1.314534 null null null 0.01493764 null null SELECT False null
8 1   |--Sort(ORDER BY:([s].[id] ASC, [Recr1002] ASC)) 1 2 1 Sort Sort ORDER BY:([s].[id] ASC, [Recr1002] ASC) null 1.314534 0.01126126 0.0001008231 14 0.01493764 [Recr1002], [s].[id] null PLAN_ROW False 1
8 1        |--Nested Loops(Inner Join, WHERE:([Recr1002]>=[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[startDate] as [s].[startDate] AND [Recr1002]<[Expr1005])) 1 3 2 Nested Loops Inner Join WHERE:([Recr1002]>=[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[startDate] as [s].[startDate] AND [Recr1002]<[Expr1005]) null 1.314534 0 3.344E-05 14 0.003575554 [Recr1002], [s].[id] null PLAN_ROW False 1
31 1             |--Index Spool(WITH STACK) 1 4 3 Index Spool Lazy Spool WITH STACK null 4 0 1E-08 10 9.414E-06 [Expr1009], [Recr1002] null PLAN_ROW False 1
31 1             | |--Concatenation 1 5 4 Concatenation Concatenation null [Expr1009] = ([Expr1006], [Expr1008]), [Recr1002] = ([@StartDate], [Expr1001]) 4 0 2E-09 10 7.824E-06 [Expr1009], [Recr1002] null PLAN_ROW False 1
0 0             | |--Compute Scalar(DEFINE:([Expr1006]=(0))) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Expr1006]=(0)) [Expr1006]=(0) 1 0 2E-08 10 2E-08 [Expr1006], [@StartDate] null PLAN_ROW False 5
1 1             | | |--Constant Scan 1 7 6 Constant Scan Constant Scan null null 1 0 1.157E-06 9 1.157E-06 null null PLAN_ROW False 1
30 1             | |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Recr1000])) 1 8 5 Nested Loops Inner Join OUTER REFERENCES:([Expr1008], [Recr1000]) null 4 0 1.68E-07 10 6.665E-06 [Expr1008], [Expr1001] null PLAN_ROW False 5
0 0             | |--Compute Scalar(DEFINE:([Expr1008]=[Expr1007]+(1))) 1 9 8 Compute Scalar Compute Scalar DEFINE:([Expr1008]=[Expr1007]+(1)) [Expr1008]=[Expr1007]+(1) 1 0 2E-08 10 2E-08 [Expr1008], [Recr1000] null PLAN_ROW False 5
31 1             | | |--Table Spool(WITH STACK) 1 10 9 Table Spool Lazy Spool WITH STACK null 1 0 2E-08 10 2E-08 [Expr1007], [Recr1000] null PLAN_ROW False 5
0 0             | |--Compute Scalar(DEFINE:([Expr1001]=dateadd(day,(1),[Recr1000]))) 1 11 8 Compute Scalar Compute Scalar DEFINE:([Expr1001]=dateadd(day,(1),[Recr1000])) [Expr1001]=dateadd(day,(1),[Recr1000]) 1 0 1E-07 10 6.477E-06 [Expr1001] null PLAN_ROW False 4
30 31             | |--Filter(WHERE:(STARTUP EXPR([Recr1000]<[@EndDate]))) 1 12 11 Filter Filter WHERE:(STARTUP EXPR([Recr1000]<[@EndDate])) null 1 0 4.8E-07 9 6.077E-06 null null PLAN_ROW False 4
30 30             | |--Constant Scan 1 13 12 Constant Scan Constant Scan null null 1 0 1.157E-06 9 4.157E-06 null null PLAN_ROW False 4
0 0             |--Compute Scalar(DEFINE:([Expr1005]=dateadd(day,[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[noDays] as [s].[noDays],[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[startDate] as [s].[startDate]))) 1 14 3 Compute Scalar Compute Scalar DEFINE:([Expr1005]=dateadd(day,[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[noDays] as [s].[noDays],[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[startDate] as [s].[startDate])) [Expr1005]=dateadd(day,[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[noDays] as [s].[noDays],[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source].[startDate] as [s].[startDate]) 2 0 2E-07 17 0.0035271 [s].[id], [s].[startDate], [Expr1005] null PLAN_ROW False 4
62 31                  |--Table Scan(OBJECT:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source] AS [s])) 1 15 14 Table Scan Table Scan OBJECT:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[tv_source] AS [s]) [s].[id], [s].[noDays], [s].[startDate] 2 0.0032035 8.07E-05 18 0.0035263 [s].[id], [s].[noDays], [s].[startDate] null PLAN_ROW False 4 
Table 'Worktable'. Scan count 2, logical reads 187, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'tv_source'. Scan count 1, logical reads 31, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
8 1 WITH cte (n, bkno, bknt, bkdt) AS
(
  SELECT
    1,
    bk_no,
    bk_nights,
    bk_sdt
  FROM booking
  UNION ALL
  SELECT
    n + 1,
    bkno,
    bknt,
    DATEADD(DAY, 1, bkdt)
  FROM cte
  WHERE n < bknt
        
)
INSERT INTO booking_nights
SELECT c.n, c.bkno, c.bkdt
FROM cte c
ORDER BY c.bkno, c.bkdt		 1 1 0 null null null null 5 null null null 0.01329934 null null INSERT False null
8 1   |--Clustered Index Insert(OBJECT:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[PK__booking___571CEF52345FA954]), SET:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[nt_no] = RaiseIfNullInsert([Recr1011]),[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[bk_no] = RaiseIfNullInsert([Recr1012]),[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[bk_night] = RaiseIfNullInsert([Recr1014]))) 1 2 1 Clustered Index Insert Insert OBJECT:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[PK__booking___571CEF52345FA954]), SET:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[nt_no] = RaiseIfNullInsert([Recr1011]),[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[bk_no] = RaiseIfNullInsert([Recr1012]),[fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking_nights].[bk_night] = RaiseIfNullInsert([Recr1014])) null 5 0.01 5E-06 9 0.01329934 null null PLAN_ROW False 1
8 1        |--Index Spool(WITH STACK) 1 3 2 Index Spool Lazy Spool WITH STACK null 5 0 1.5E-08 18 0.003294337 [Expr1018], [Recr1011], [Recr1012], [Recr1013], [Recr1014] null PLAN_ROW False 1
8 1             |--Concatenation 1 4 3 Concatenation Concatenation null [Expr1018] = ([Expr1015], [Expr1017]), [Recr1011] = ([Expr1004], [Expr1009]), [Recr1012] = ([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_no], [Recr1006]), [Recr1013] = ([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_nights], [Recr1007]), [Recr1014] = ([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_sdt], [Expr1010]) 5 0 3E-09 18 0.003292742 [Expr1018], [Recr1011], [Recr1012], [Recr1013], [Recr1014] null PLAN_ROW False 1
0 0                  |--Compute Scalar(DEFINE:([Expr1015]=(0))) 1 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1015]=(0)) [Expr1015]=(0) 1 0 3E-08 18 3E-08 [Expr1015], [Expr1004], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_no], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_nights], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_sdt] null PLAN_ROW False 6
0 0                  | |--Compute Scalar(DEFINE:([Expr1004]=(1))) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Expr1004]=(1)) [Expr1004]=(1) 2 0 2E-07 22 0.0032844 [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_no], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_nights], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_sdt], [Expr1004] null PLAN_ROW False 1
2 1                  | |--Clustered Index Scan(OBJECT:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[PK__booking__AFB449124A40FED4])) 1 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[PK__booking__AFB449124A40FED4]) [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_no], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_nights], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_sdt] 2 0.003125 0.0001592 18 0.0032842 [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_no], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_nights], [fiddle_ebac1788b1824eaab0a516348dd63613].[dbo].[booking].[bk_sdt] null PLAN_ROW False 1
6 1                  |--Assert(WHERE:(CASE WHEN [Expr1017]>(100) THEN (0) ELSE NULL END)) 1 8 4 Assert Assert WHERE:(CASE WHEN [Expr1017]>(100) THEN (0) ELSE NULL END) null 2.5 0 2.52E-07 18 8.339E-06 [Expr1017], [Expr1009], [Recr1006], [Recr1007], [Expr1010] null PLAN_ROW False 6
6 1                       |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1017], [Recr1005], [Recr1006], [Recr1007], [Recr1008])) 1 9 8 Nested Loops Inner Join OUTER REFERENCES:([Expr1017], [Recr1005], [Recr1006], [Recr1007], [Recr1008]) null 2.5 0 2.52E-07 18 8.339E-06 [Expr1017], [Expr1009], [Recr1006], [Recr1007], [Expr1010] null PLAN_ROW False 6
0 0                            |--Compute Scalar(DEFINE:([Expr1017]=[Expr1016]+(1))) 1 10 9 Compute Scalar Compute Scalar DEFINE:([Expr1017]=[Expr1016]+(1)) [Expr1017]=[Expr1016]+(1) 1 0 3E-08 18 3E-08 [Expr1017], [Recr1005], [Recr1006], [Recr1007], [Recr1008] null PLAN_ROW False 6
8 1                            | |--Table Spool(WITH STACK) 1 11 10 Table Spool Lazy Spool WITH STACK null 1 0 3E-08 18 3E-08 [Expr1016], [Recr1005], [Recr1006], [Recr1007], [Recr1008] null PLAN_ROW False 6
0 0                            |--Compute Scalar(DEFINE:([Expr1009]=[Recr1005]+(1), [Expr1010]=dateadd(day,(1),[Recr1008]))) 1 12 9 Compute Scalar Compute Scalar DEFINE:([Expr1009]=[Recr1005]+(1), [Expr1010]=dateadd(day,(1),[Recr1008])) [Expr1009]=[Recr1005]+(1), [Expr1010]=dateadd(day,(1),[Recr1008]) 1 0 1E-07 14 8.057E-06 [Expr1009], [Expr1010] null PLAN_ROW False 5
6 8                                 |--Filter(WHERE:(STARTUP EXPR([Recr1005]<[Recr1007]))) 1 13 12 Filter Filter WHERE:(STARTUP EXPR([Recr1005]<[Recr1007])) null 1 0 4.8E-07 9 7.557E-06 null null PLAN_ROW False 5
6 6                                      |--Constant Scan 1 14 13 Constant Scan Constant Scan null null 1 0 1.157E-06 9 5.157E-06 null null PLAN_ROW False 5 
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'booking_nights'. Scan count 0, logical reads 17, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 47, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'booking'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.