Different results while inserting into temp table and table variable

Question

When I'm using a table variable to store queried result like this:

INSERT INTO @sortedArticleIds
    SELECT article_id, NULL AS groupBy
    FROM #articleIds a
    GROUP BY article_id
    ORDER BY MIN(sortBy) DESC;

the rows inserted to @sortedArticleIds change randomly.

But if I use #table like this:

INSERT INTO #tmp_table
    SELECT article_id, NULL AS groupBy
    FROM #articleIds a
    GROUP BY article_id
    ORDER BY MIN(sortBy) DESC;

the rows inserted to #tmp_table are always same.

I'm using SQL Server 2008 R2.

Answer 1

In relational database, your table is a set. It means that the ORDER BY and your GROUP BY of you insert is not needed.

INSERT INTO @sortedArticleIds
SELECT article_id, NULL AS groupBy
FROM #articleIds

Here you are updating a table, so we don't need an ORDER BY clause.

But when you will query your table, prefer a query like this.

SELECT *
FROM @sortedArticleIds
GROUP BY article_id
ORDER BY MIN(sortBy) DESC;