SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.CustomerNote
(
    CustomerID integer NOT NULL,
    [Date] datetime NOT NULL
);
WITH 
    N (n) AS
    (
        SELECT 
            SV.number 
        FROM master.dbo.spt_values AS SV
        WHERE
            SV.[type] = N'P'
            AND SV.number >= 1
    )
INSERT dbo.CustomerNote
    WITH (TABLOCKX)
(
    CustomerID, 
    [Date]
)
SELECT 
    C.n, 
    DATEADD
    (
        MINUTE, 
        -D.n * RAND(CHECKSUM(NEWID())) * 1000,
        GETDATE()
    )
FROM N AS C
CROSS JOIN N AS D
WHERE
    C.n BETWEEN 1 AND 9;

CREATE NONCLUSTERED INDEX 
    [IX dbo.CustomerNote CustomerID, Date-] 
ON dbo.CustomerNote
    (CustomerID, [Date] DESC);
18423 rows affected

SELECT CN.CustomerID, NumRows = COUNT_BIG(*) 
FROM dbo.CustomerNote AS CN
GROUP BY CN.CustomerID
ORDER BY CN.CustomerID;


CustomerID
NumRows


1
2047

2
2047

3
2047

4
2047

5
2047

6
2047

7
2047

8
2047

9
2047


SET STATISTICS PROFILE ON;

SELECT TOP (30) 
    CN.[Date] 
FROM dbo.CustomerNote AS CN
WHERE 
    CN.CustomerID IN (SELECT 1 UNION SELECT 5)
ORDER BY 
    CN.[Date] DESC;


Date


2023-07-11 12:52:12.440

2023-07-11 12:28:12.440

2023-07-11 12:11:12.440

2023-07-11 11:36:12.440

2023-07-11 11:18:12.440

2023-07-11 08:15:12.440

2023-07-11 07:53:12.440

2023-07-11 07:47:12.440

2023-07-11 06:51:12.440

2023-07-11 06:27:12.440

2023-07-11 06:23:12.440

2023-07-11 04:54:12.440

2023-07-11 04:41:12.440

2023-07-11 04:20:12.440

2023-07-11 00:17:12.440

2023-07-10 23:00:12.440

2023-07-10 21:36:12.440

2023-07-10 21:02:12.440

2023-07-10 20:45:12.440

2023-07-10 20:06:12.440

2023-07-10 19:23:12.440

2023-07-10 19:21:12.440

2023-07-10 18:40:12.440

2023-07-10 18:25:12.440

2023-07-10 18:14:12.440

2023-07-10 18:11:12.440

2023-07-10 14:50:12.440

2023-07-10 11:20:12.440

2023-07-10 09:57:12.440

2023-07-10 08:13:12.440




Rows
Executes
StmtText
StmtId
NodeId
Parent
PhysicalOp
LogicalOp
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubtreeCost
OutputList
Warnings
Type
Parallel
EstimateExecutions


30
1
SELECT TOP (30) 
    CN.[Date] 
FROM dbo.CustomerNote AS CN
WHERE 
    CN.CustomerID IN (SELECT 1 UNION SELECT 5)
ORDER BY 
    CN.[Date] DESC
1
1
0
null
null
null
null
30
null
null
null
0.01289201
null
null
SELECT
False
null

30
1
  |--Top(TOP EXPRESSION:((30)))
1
2
1
Top
Top
TOP EXPRESSION:((30))
null
30
0
3E-06
15
0.01289201
[CN].[Date]
null
PLAN_ROW
False
1

30
1
       |--Merge Join(Concatenation)
1
4
2
Merge Join
Concatenation
null
[CN].[Date] = ([CN].[Date], [CN].[Date])
30
0
0.0146068
15
0.01288901
[CN].[Date]
null
PLAN_ROW
False
1

13
1
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))
1
5
4
Nested Loops
Inner Join
OUTER REFERENCES:([Expr1002])
null
30
0
0.00855646
15
0.003527453
[CN].[Date]
null
PLAN_ROW
False
1

1
1
            |    |--Constant Scan(VALUES:(((1))))
1
6
5
Constant Scan
Constant Scan
VALUES:(((1)))
null
1
0
1.157E-06
11
1.157E-06
[Expr1002]
null
PLAN_ROW
False
1

13
1
            |    |--Index Seek(OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]),  WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)
1
7
5
Index Seek
Index Seek
OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]),  WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD
[Bmk1000], [CN].[CustomerID], [CN].[Date]
30
0.007322531
0.0024087
27
0.003374496
[Bmk1000], [CN].[CustomerID], [CN].[Date]
null
PLAN_ROW
False
1

18
1
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003]))
1
8
4
Nested Loops
Inner Join
OUTER REFERENCES:([Expr1003])
null
1
0
0.00855646
15
0.003289317
[CN].[Date]
null
PLAN_ROW
False
1

1
1
                 |--Constant Scan(VALUES:(((5))))
1
9
8
Constant Scan
Constant Scan
VALUES:(((5)))
null
1
0
1.157E-06
11
1.157E-06
[Expr1003]
null
PLAN_ROW
False
1

18
1
                 |--Index Seek(OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]),  WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)
1
10
8
Index Seek
Index Seek
OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]),  WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD
[Bmk1000], [CN].[CustomerID], [CN].[Date]
1
0.007322531
0.0024087
27
0.0032831
[Bmk1000], [CN].[CustomerID], [CN].[Date]
null
PLAN_ROW
False
1

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
30	1	SELECT TOP (30) CN.[Date] FROM dbo.CustomerNote AS CN WHERE CN.CustomerID IN (SELECT 1 UNION SELECT 5) ORDER BY CN.[Date] DESC	1	1	0	null	null	null	null	30	null	null	null	0.01289201	null	null	SELECT	False	null
30	1	\|--Top(TOP EXPRESSION:((30)))	1	2	1	Top	Top	TOP EXPRESSION:((30))	null	30	0	3E-06	15	0.01289201	[CN].[Date]	null	PLAN_ROW	False	1
30	1	\|--Merge Join(Concatenation)	1	4	2	Merge Join	Concatenation	null	[CN].[Date] = ([CN].[Date], [CN].[Date])	30	0	0.0146068	15	0.01288901	[CN].[Date]	null	PLAN_ROW	False	1
13	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))	1	5	4	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1002])	null	30	0	0.00855646	15	0.003527453	[CN].[Date]	null	PLAN_ROW	False	1
1	1	\| \|--Constant Scan(VALUES:(((1))))	1	6	5	Constant Scan	Constant Scan	VALUES:(((1)))	null	1	0	1.157E-06	11	1.157E-06	[Expr1002]	null	PLAN_ROW	False	1
13	1	\| \|--Index Seek(OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]), WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)	1	7	5	Index Seek	Index Seek	OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]), WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD	[Bmk1000], [CN].[CustomerID], [CN].[Date]	30	0.007322531	0.0024087	27	0.003374496	[Bmk1000], [CN].[CustomerID], [CN].[Date]	null	PLAN_ROW	False	1
18	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003]))	1	8	4	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1003])	null	1	0	0.00855646	15	0.003289317	[CN].[Date]	null	PLAN_ROW	False	1
1	1	\|--Constant Scan(VALUES:(((5))))	1	9	8	Constant Scan	Constant Scan	VALUES:(((5)))	null	1	0	1.157E-06	11	1.157E-06	[Expr1003]	null	PLAN_ROW	False	1
18	1	\|--Index Seek(OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]), WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)	1	10	8	Index Seek	Index Seek	OBJECT:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]), WHERE:([fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_adfc51cc37984312ad5ddc58579213db].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD	[Bmk1000], [CN].[CustomerID], [CN].[Date]	1	0.007322531	0.0024087	27	0.0032831	[Bmk1000], [CN].[CustomerID], [CN].[Date]	null	PLAN_ROW	False	1