SQL Server 2022

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

CREATE TABLE dbo.CustomerNote
(
    SomeKey integer IDENTITY NOT NULL PRIMARY KEY,
    CustomerID integer NOT NULL,
    [Date] datetime NOT NULL,
    Note varchar(400) 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

SET STATISTICS PROFILE ON;

SELECT TOP (30) 
    FN.Note
FROM dbo.CustomerNote AS CN
JOIN dbo.CustomerNote AS FN
    ON FN.SomeKey = CN.SomeKey
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) 
    FN.Note
FROM dbo.CustomerNote AS CN
JOIN dbo.CustomerNote AS FN
    ON FN.SomeKey = CN.SomeKey
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.09982941
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.09982941
[FN].[Note]
null
PLAN_ROW
False
1

30
1
       |--Nested Loops(Inner Join, OUTER REFERENCES:([CN].[SomeKey], [Expr1009]) WITH ORDERED PREFETCH)
1
4
2
Nested Loops
Inner Join
OUTER REFERENCES:([CN].[SomeKey], [Expr1009]) WITH ORDERED PREFETCH
null
30
0
0.01711292
219
0.09982641
[CN].[Date], [FN].[Note]
null
PLAN_ROW
False
1

30
1
            |--Merge Join(Concatenation)
1
6
4
Merge Join
Concatenation
null
[CN].[Date] = ([CN].[Date], [CN].[Date]), [CN].[SomeKey] = ([CN].[SomeKey], [CN].[SomeKey])
30
0
0.0146068
19
0.01287852
[CN].[Date], [CN].[SomeKey]
null
PLAN_ROW
False
1

14
1
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))
1
7
6
Nested Loops
Inner Join
OUTER REFERENCES:([Expr1002])
null
30
0
0.00855646
19
0.003516953
[CN].[SomeKey], [CN].[Date]
null
PLAN_ROW
False
1

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

14
1
            |    |    |--Index Seek(OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]),  WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)
1
9
7
Index Seek
Index Seek
OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]),  WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD
[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]
30
0.00658179
0.0024087
23
0.003363997
[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]
null
PLAN_ROW
False
1

17
1
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003]))
1
10
6
Nested Loops
Inner Join
OUTER REFERENCES:([Expr1003])
null
1
0
0.00855646
19
0.003289317
[CN].[SomeKey], [CN].[Date]
null
PLAN_ROW
False
1

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

17
1
            |         |--Index Seek(OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]),  WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)
1
12
10
Index Seek
Index Seek
OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]),  WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD
[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]
1
0.00658179
0.0024087
23
0.0032831
[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]
null
PLAN_ROW
False
1

30
30
            |--Clustered Index Seek(OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[PK__Customer__9E8B85C4DF11BBC2] AS [FN]), SEEK:([FN].[SomeKey]=[fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[SomeKey] as [CN].[SomeKey]) ORDERED FORWARD)
1
13
4
Clustered Index Seek
Clustered Index Seek
OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[PK__Customer__9E8B85C4DF11BBC2] AS [FN]), SEEK:([FN].[SomeKey]=[fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[SomeKey] as [CN].[SomeKey]) ORDERED FORWARD
[FN].[Note]
1
0.003125
0.0001581
211
0.08682249
[FN].[Note]
null
PLAN_ROW
False
30.99267

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) FN.Note FROM dbo.CustomerNote AS CN JOIN dbo.CustomerNote AS FN ON FN.SomeKey = CN.SomeKey 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.09982941	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.09982941	[FN].[Note]	null	PLAN_ROW	False	1
30	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([CN].[SomeKey], [Expr1009]) WITH ORDERED PREFETCH)	1	4	2	Nested Loops	Inner Join	OUTER REFERENCES:([CN].[SomeKey], [Expr1009]) WITH ORDERED PREFETCH	null	30	0	0.01711292	219	0.09982641	[CN].[Date], [FN].[Note]	null	PLAN_ROW	False	1
30	1	\|--Merge Join(Concatenation)	1	6	4	Merge Join	Concatenation	null	[CN].[Date] = ([CN].[Date], [CN].[Date]), [CN].[SomeKey] = ([CN].[SomeKey], [CN].[SomeKey])	30	0	0.0146068	19	0.01287852	[CN].[Date], [CN].[SomeKey]	null	PLAN_ROW	False	1
14	1	\| \|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002]))	1	7	6	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1002])	null	30	0	0.00855646	19	0.003516953	[CN].[SomeKey], [CN].[Date]	null	PLAN_ROW	False	1
1	1	\| \| \|--Constant Scan(VALUES:(((1))))	1	8	7	Constant Scan	Constant Scan	VALUES:(((1)))	null	1	0	1.157E-06	11	1.157E-06	[Expr1002]	null	PLAN_ROW	False	1
14	1	\| \| \|--Index Seek(OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]), WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)	1	9	7	Index Seek	Index Seek	OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1002]), WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD	[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]	30	0.00658179	0.0024087	23	0.003363997	[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]	null	PLAN_ROW	False	1
17	1	\| \|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003]))	1	10	6	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1003])	null	1	0	0.00855646	19	0.003289317	[CN].[SomeKey], [CN].[Date]	null	PLAN_ROW	False	1
1	1	\| \|--Constant Scan(VALUES:(((5))))	1	11	10	Constant Scan	Constant Scan	VALUES:(((5)))	null	1	0	1.157E-06	11	1.157E-06	[Expr1003]	null	PLAN_ROW	False	1
17	1	\| \|--Index Seek(OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]), WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD)	1	12	10	Index Seek	Index Seek	OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[IX dbo.CustomerNote CustomerID, Date-] AS [CN]), SEEK:([CN].[CustomerID]=[Expr1003]), WHERE:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]>=(1) AND [fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[CustomerID] as [CN].[CustomerID]<=(5)) ORDERED FORWARD	[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]	1	0.00658179	0.0024087	23	0.0032831	[CN].[SomeKey], [CN].[CustomerID], [CN].[Date]	null	PLAN_ROW	False	1
30	30	\|--Clustered Index Seek(OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[PK__Customer__9E8B85C4DF11BBC2] AS [FN]), SEEK:([FN].[SomeKey]=[fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[SomeKey] as [CN].[SomeKey]) ORDERED FORWARD)	1	13	4	Clustered Index Seek	Clustered Index Seek	OBJECT:([fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[PK__Customer__9E8B85C4DF11BBC2] AS [FN]), SEEK:([FN].[SomeKey]=[fiddle_6fd04c4850424da38ad54b1c0e554a4e].[dbo].[CustomerNote].[SomeKey] as [CN].[SomeKey]) ORDERED FORWARD	[FN].[Note]	1	0.003125	0.0001581	211	0.08682249	[FN].[Note]	null	PLAN_ROW	False	30.99267