Here is a little snippet to play around with, mind you one cannot blindly read the statistics output;-)

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SET_SIZE INT = 1000000;

DECLARE @BUCKET INT = 0;

CREATE TABLE dbo.NON_INDEXED_NUMS (N INT PRIMARY KEY CLUSTERED NOT NULL);

;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(@SET_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)

INSERT INTO dbo.NON_INDEXED_NUMS(N)

SELECT N FROM NUMS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP(@SET_SIZE) @BUCKET = N

FROM dbo.NON_INDEXED_NUMS;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP(@SET_SIZE) @BUCKET = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM dbo.NON_INDEXED_NUMS;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP(@SET_SIZE) @BUCKET = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.columns C1,sys.columns C2,sys.columns C3;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

;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(@SET_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)

SELECT @BUCKET = N FROM NUMS;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

;WITH

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), -- 1*10^1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows

E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows

SELECT TOP (@SET_SIZE) @BUCKET = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

DROP TABLE dbo.NON_INDEXED_NUMS;

Results (on my mediocre laptop)

SQL Server parse and compile time:

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

Table 'NON_INDEXED_NUMS'. Scan count 1, logical reads 1615, 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 = 125 ms, elapsed time = 117 ms.

SQL Server parse and compile time:

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

Table 'NON_INDEXED_NUMS'. Scan count 1, logical reads 1615, 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 = 190 ms.

Table 'syscolpars'. Scan count 3, logical reads 23, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 133 ms.

SQL Server Execution Times:

CPU time = 140 ms, elapsed time = 140 ms.

SQL Server Execution Times:

CPU time = 141 ms, elapsed time = 137 ms.