Random key-based SELECT query

Question

Is it possible to randomize SELECT based on a key?

I used this query for a random query:

SELECT * FROM tbl_news ORDER BY NEWID();

Is there a key-based function or method similar to random :

SELECT * FROM tbl_news ORDER BY NEWID(15);

5,1,3,2,4

Using the same key :

SELECT * FROM tbl_news ORDER BY NEWID(15);

5,1,3,2,4

Using another :

SELECT * FROM tbl_news ORDER BY NEWID(36);

2,5,1,3,4

Answer 1

You can use HASHBYTES, although it may not perform brilliantly on a large number of lines (although not NEWID()), for example,

-- CREATE SAMPLE DATA
IF OBJECT_ID(N'tempdb..#tbl_news', 'U') IS NOT NULL DROP TABLE #tbl_news;
CREATE TABLE #tbl_news (NewsID INT IDENTITY);
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;

-- DEFINE YOUR SORT KEY
DECLARE @Key INT = 15;

SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));

-- SAME ORDER AS FIRST SELECT TO SHOW SORT IS REPEATABLE
SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));


SET @Key = 36;

-- WITH A NEW KEY SHOW DIFFERENT ORDER
SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));

-- BUT NEW ORDER IS STILL REPEATABLE
SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));

Answer 2

, , . . . . , , , , . . .

DECLARE @SEED INT = 100;
DECLARE @SIZE INT = 5;
-- url http://www.codeproject.com/Tips/811913/Generating-a-set-of-random-numbers-in-SQL-Server
WITH RandomNumbers (RowNumber, RandomNumber) AS (
    -- Anchor member definition
    SELECT  1                         AS RowNumber, 
            RAND(@SEED) AS RandomNumber
    UNION ALL
    -- Recursive member definition
    SELECT  rn.RowNumber + 1          AS RowNumber, 
            RAND( 1000000000* RAND(@SEED + rn.RowNumber))  AS RandomNumber
    FROM RandomNumbers rn
    WHERE rn.RowNumber < @SIZE
)
-- Statement that executes the CTE
SELECT  rn.RowNumber, rn.RandomNumber
FROM RandomNumbers rn
INNER JOIN tbl_news n
ON rn.RowNumber = n.ID
ORDER BY rn.RandomNumber