CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2temp-tablestable-variable
Chinthaka Fernando
Chinthaka Fernando

Reputation: 814

Different results while inserting into temp table and table variable

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.

Upvotes: 1

Views: 646

Answers (1)

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

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;

Upvotes: 1

Related Questions