CROSS APPLY - scrappyCoco/SQL-Note GitHub Wiki

Let's imagine that we need to fetch 100 random rows from the table #Entities. One note: we can't use TABLESAMPLE; SQL ABS(CHECKSUM(CAST(NEWID() AS CHAR(36)))) - is random number.

Our code:

-- region Generating numbers 1 to 65536.
CREATE TABLE #Number (Number INT NOT NULL PRIMARY KEY);

WITH N1 AS (SELECT N = 0 UNION ALL SELECT N = 0), --> 2
     N2 AS (SELECT N1.N FROM N1 AS NN CROSS JOIN N1), --> 4
     N3 AS (SELECT N2.N FROM N2 AS NN CROSS JOIN N2), --> 16
     N4 AS (SELECT N3.N FROM N3 AS NN CROSS JOIN N3), --> 256
     N5 AS (SELECT N4.N FROM N4 AS NN CROSS JOIN N4) --> 65K
INSERT INTO #Number (Number)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM N5;
-- endregion

-- region Something entities
CREATE TABLE #Entity (
    Id    INT NOT NULL PRIMARY KEY,
    Value VARCHAR(100)
);

INSERT INTO #Entity (Id, Value)
SELECT Number, NEWID()
FROM #Number;
-- endregion

-- region Random entities from
CREATE TABLE #RandomEntity (
    Id    INT,
    Value VARCHAR(100)
);
-- endregion

-- SELECT MAX(Id) FROM #RandomEntity.
DECLARE @maxEntityId INT = 65536;

INSERT #RandomEntity (Id, Value)
SELECT Id, Value
FROM #Number
CROSS APPLY (
    SELECT TOP (1) Id, Value
    FROM #Entity
    WHERE Id >= ABS(CHECKSUM(CAST(NEWID() AS CHAR(36)))) % @maxEntityId
) AS E
WHERE Number <= 100;

SELECT COUNT(DISTINCT Value), COUNT(*)
FROM #RandomEntity;

DROP TABLE #RandomEntity
DROP TABLE #Number
DROP TABLE #Entity

What will be returned in COUNT(DISTINCT Value), COUNT(*)?