How can I shuffle rows in a SQL Server table after insert? Newid doesn't work as expected for some reasons

I'm currently working on a SQL query to retrieve data from two tables and store it into a temp table called @folderTable. I want to shuffle all the rows in the result so that the entire row's data is randomized

Here's a simplified version of my query:

DECLARE @folderTable TABLE (FolderId int, QuestionsPerExam int, AvailableQuestions int);

INSERT INTO @folderTable (FolderId, QuestionsPerExam, AvailableQuestions)
    SELECT f.id, f.questions_per_exam, COUNT(q.id)
    FROM tbl_mcq_folder f
    LEFT JOIN tbl_mcq_questions q ON q.folder_id = f.id
    WHERE f.collaborator_id = 5 AND f.status = 1
    GROUP BY f.id, f.questions_per_exam
    ORDER BY NEWID();

After executing this, I want the rows in @folderTable to be randomized, similar to this:

Before shuffle:

FolderId	QuestionsPerExam	AvailableQuestions
1	5	10
2	3	15
3	7	20

After shuffle:

FolderId	QuestionsPerExam	AvailableQuestions
2	3	15
1	5	10
3	7	20

I tried using ORDER BY NEWID(), but it doesn't seem to be working as expected within the context of my temp table.

Is there a way to shuffle all the rows in @folderTable so that each row's data appears in a random order?

Solution

The key concept to understand here is that SQL tables are based on unordered sets of records. There is no inherent internal order to the records in the @folderTable after the insert. If you want to view your data in a random order, then NEWID() should be used in the select rather than the insert:

SELECT FolderId, QuestionsPerExam, AvailableQuestions
FROM @folderTable
ORDER BY NEWID();

Note that the ORDER BY NEWID() in your current insert statement will essentially be a no-op, although it might make the insert slower.