Top 1 doesn't have to be a problem or rather all depends, consider these samples

😎

USE tempdb;

GO

SET NOCOUNT ON;

/********************************************************************

Sample data set

********************************************************************/

DECLARE @SAMPLE_SIZE INT = 10000;

DECLARE @CODE_COUNT INT = 100;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_T2') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_T2;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_T1') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_T1;

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

SELECT

ISNULL(CONVERT(INT,NM.N,0),0) AS T1_ID

,REPLACE(NEWID(),'-','') AS T1_TEXT

INTO dbo.TBL_SAMPLE_T1

FROM NUMS NM;

ALTER TABLE dbo.TBL_SAMPLE_T1 ADD CONSTRAINT PK_DBO_TBL_SAMPLE_T1_T1_ID PRIMARY KEY CLUSTERED (T1_ID ASC);

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE * @CODE_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

SELECT

ISNULL(NM.N,0) AS T2_ID

,ISNULL(CONVERT(INT,(ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE) + 1,0),1) AS T1_ID

,SUBSTRING(REPLACE(NEWID(),'-',''),1,8) AS T2_CODE

INTO dbo.TBL_SAMPLE_T2

FROM NUMS NM;

ALTER TABLE dbo.TBL_SAMPLE_T2 ADD CONSTRAINT PK_DBO_TBL_SAMPLE_T2_T2_ID PRIMARY KEY CLUSTERED (T2_ID ASC);

ALTER TABLE dbo.TBL_SAMPLE_T2 ADD CONSTRAINT FK_DBO_TBL_SAMPLE_T2_T1_ID_DBO_TBL_SAMPLE_T1_T1_ID FOREIGN KEY (T1_ID) REFERENCES dbo.TBL_SAMPLE_T1( T1_ID );

/********************************************************************

Test harness

********************************************************************/

DECLARE @INT_BUCKET INT = 0;

DECLARE @CHR_BUCKET CHAR(10) = '';

RAISERROR(N'---------------------------------------------------------------------

SUBQUERY METHOD NO INDEX

---------------------------------------------------------------------',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

SELECT

@INT_BUCKET = T1.T1_ID

,@CHR_BUCKET = (SELECT

TOP(1) T2.T2_CODE

FROM dbo.TBL_SAMPLE_T2 T2

WHERE T1.T1_ID = T2.T1_ID

ORDER BY T2.T2_ID DESC) --AS T2_CODE

FROM dbo.TBL_SAMPLE_T1 T1;

SET STATISTICS TIME,IO OFF;

RAISERROR(N'---------------------------------------------------------------------

CROSS APPLY METHOD NO INDEX

---------------------------------------------------------------------',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

SELECT

@INT_BUCKET = T1.T1_ID

,@CHR_BUCKET = TX.T2_CODE

FROM dbo.TBL_SAMPLE_T1 T1

CROSS APPLY (

SELECT

T2.T1_ID

,MAX(T2.T2_ID) AS T2_ID

FROM dbo.TBL_SAMPLE_T2 T2

GROUP BY T2.T1_ID

) AS X

CROSS APPLY dbo.TBL_SAMPLE_T2 TX

WHERE T1.T1_ID = X.T1_ID

AND X.T2_ID = TX.T2_ID;

SET STATISTICS TIME,IO OFF;

RAISERROR(N'---------------------------------------------------------------------

PP WINDOW SET NO INDEX

---------------------------------------------------------------------',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

;WITH OrderedSet AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY t2.T1_ID

ORDER BY t2.T2_CODE asc

,t2.T2_ID desc

) AS T2_RID

,T2.T1_ID

,t2.T2_CODE

FROM dbo.TBL_SAMPLE_T2 t2

)

SELECT

@INT_BUCKET = t1.T1_ID

,@CHR_BUCKET = OS.T2_CODE

FROM dbo.TBL_SAMPLE_T1 t1

INNER JOIN OrderedSet OS

ON t1.T1_ID = OS.T1_ID

WHERE OS.T2_RID = 1;SET STATISTICS TIME,IO OFF;

CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_TBL_SAMPLE_T2_T1_ID_T2_ID_INCL_T2_CODE ON dbo.TBL_SAMPLE_T2

( T1_ID ASC, T2_ID DESC )

INCLUDE

( T2_CODE);

RAISERROR(N'---------------------------------------------------------------------

SUBQUERY METHOD WITH INDEX

---------------------------------------------------------------------',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

SELECT

@INT_BUCKET = T1.T1_ID

,@CHR_BUCKET = (SELECT

TOP(1) T2.T2_CODE

FROM dbo.TBL_SAMPLE_T2 T2

WHERE T1.T1_ID = T2.T1_ID

ORDER BY T2.T2_ID DESC) --AS T2_CODE

FROM dbo.TBL_SAMPLE_T1 T1;

SET STATISTICS TIME,IO OFF;

RAISERROR(N'---------------------------------------------------------------------

CROSS APPLY METHOD WITH INDEX

---------------------------------------------------------------------',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

SELECT

@INT_BUCKET = T1.T1_ID

,@CHR_BUCKET = TX.T2_CODE

FROM dbo.TBL_SAMPLE_T1 T1

CROSS APPLY (

SELECT

T2.T1_ID

,MAX(T2.T2_ID) AS T2_ID

FROM dbo.TBL_SAMPLE_T2 T2

GROUP BY T2.T1_ID

) AS X

CROSS APPLY dbo.TBL_SAMPLE_T2 TX

WHERE T1.T1_ID = X.T1_ID

AND X.T2_ID = TX.T2_ID;

SET STATISTICS TIME,IO OFF;

RAISERROR(N'---------------------------------------------------------------------

PP WINDOW SET WITH INDEX

---------------------------------------------------------------------',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

;WITH OrderedSet AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY t2.T1_ID

ORDER BY t2.T2_CODE asc

,t2.T2_ID desc

) AS T2_RID

,T2.T1_ID

,t2.T2_CODE

FROM dbo.TBL_SAMPLE_T2 t2

)

SELECT

@INT_BUCKET = t1.T1_ID

,@CHR_BUCKET = OS.T2_CODE

FROM dbo.TBL_SAMPLE_T1 t1

INNER JOIN OrderedSet OS

ON t1.T1_ID = OS.T1_ID

WHERE OS.T2_RID = 1;SET STATISTICS TIME,IO OFF;

Output

---------------------------------------------------------------------

SUBQUERY METHOD NO INDEX

---------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 546391, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 6614 ms, elapsed time = 6642 ms.

---------------------------------------------------------------------

CROSS APPLY METHOD NO INDEX

---------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 9 ms.

Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 34731, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 250 ms, elapsed time = 250 ms.

---------------------------------------------------------------------

PP WINDOW SET NO INDEX

---------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 3 ms, elapsed time = 3 ms.

Table 'TBL_SAMPLE_T1'. Scan count 5, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TBL_SAMPLE_T2'. Scan count 5, logical reads 4139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3806 ms, elapsed time = 1016 ms.

---------------------------------------------------------------------

SUBQUERY METHOD WITH INDEX

---------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table 'TBL_SAMPLE_T2'. Scan count 10000, logical reads 31945, physical reads 0, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 36 ms.

---------------------------------------------------------------------

CROSS APPLY METHOD WITH INDEX

---------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 12 ms, elapsed time = 12 ms.

Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 34367, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 187 ms, elapsed time = 196 ms.

---------------------------------------------------------------------

PP WINDOW SET WITH INDEX

---------------------------------------------------------------------

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 3 ms.

Table 'TBL_SAMPLE_T1'. Scan count 5, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TBL_SAMPLE_T2'. Scan count 5, logical reads 3779, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3168 ms, elapsed time = 857 ms.

Edit: added Phil's method