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.

What you means by *the rows inserted to @sortedArticleIds change randomly* ? — Arnaud Peralta, Apr 25 '19 at 05:19
when i execute same query again, the records insert to variable changes randomly. but `#articleIds` holds same records. — Chinthaka Fernando, Apr 25 '19 at 05:20
The order of the rows inserted is insignificant, Always use order by while fetching results from a table even if the records look like they are already stored in order. — Niranjan Rajawat, Apr 25 '19 at 05:21

score 1 · Accepted Answer · answered Apr 25 '19 at 05:24

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;

answered Apr 25 '19 at 05:24

Arnaud Peralta

Thanks for the help, but when i use #table this returns same results. is there any deference using table variable vs #table in above case. – Chinthaka Fernando Apr 25 '19 at 05:27
You can enable statistics before the query to check if there is a difference. – Arnaud Peralta Apr 25 '19 at 08:54

1 Answers1