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,
    Note varchar(400)
);
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-, Note] 
ON dbo.CustomerNote
    (CustomerID, [Date] DESC, Note);
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.[Note] 
FROM dbo.CustomerNote AS CN
WHERE 
    CN.CustomerID IN (SELECT 1 UNION SELECT 5)
ORDER BY 
    CN.[Date] DESC;


Note


null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null




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.[Note] 
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
211
0.01289201
[CN].[Note]
null
PLAN_ROW
False
1

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

17
1
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))
1
5
4
Nested Loops
Inner Join
OUTER REFERENCES:([Expr1002])
null
30
0
0.00855646
219
0.003527453
[CN].[Date], [CN].[Note]
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

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

14
1
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003]))
1
8
4
Nested Loops
Inner Join
OUTER REFERENCES:([Expr1003])
null
1
0
0.00855646
219
0.003289317
[CN].[Date], [CN].[Note]
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

14
1
                 |--Index Seek(OBJECT:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-, Note] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]),  WHERE:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)
1
10
8
Index Seek
Index Seek
OBJECT:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-, Note] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]),  WHERE:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD
[Bmk1000], [CN].[CustomerID], [CN].[Date], [CN].[Note]
1
0.007322531
0.0024087
231
0.0032831
[Bmk1000], [CN].[CustomerID], [CN].[Date], [CN].[Note]
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.[Note] 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	211	0.01289201	[CN].[Note]	null	PLAN_ROW	False	1
30	1	\|--Merge Join(Concatenation)	1	4	2	Merge Join	Concatenation	null	[CN].[Date] = ([CN].[Date], [CN].[Date]), [CN].[Note] = ([CN].[Note], [CN].[Note])	30	0	0.0146068	219	0.01288901	[CN].[Date], [CN].[Note]	null	PLAN_ROW	False	1
17	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))	1	5	4	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1002])	null	30	0	0.00855646	219	0.003527453	[CN].[Date], [CN].[Note]	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
17	1	\| \|--Index Seek(OBJECT:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-, Note] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]), WHERE:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)	1	7	5	Index Seek	Index Seek	OBJECT:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-, Note] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]), WHERE:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD	[Bmk1000], [CN].[CustomerID], [CN].[Date], [CN].[Note]	30	0.007322531	0.0024087	231	0.003374496	[Bmk1000], [CN].[CustomerID], [CN].[Date], [CN].[Note]	null	PLAN_ROW	False	1
14	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003]))	1	8	4	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1003])	null	1	0	0.00855646	219	0.003289317	[CN].[Date], [CN].[Note]	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
14	1	\|--Index Seek(OBJECT:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-, Note] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]), WHERE:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)	1	10	8	Index Seek	Index Seek	OBJECT:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-, Note] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]), WHERE:([fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_f744f008df7c4aa5af510a6868303512].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD	[Bmk1000], [CN].[CustomerID], [CN].[Date], [CN].[Note]	1	0.007322531	0.0024087	231	0.0032831	[Bmk1000], [CN].[CustomerID], [CN].[Date], [CN].[Note]	null	PLAN_ROW	False	1