INSERT ... SELECT ... ORDER BY consistency of results

Question

Post reply

INSERT ... SELECT ... ORDER BY consistency of results

Tom257

SSChampion

Points: 11543
More actions
November 14, 2011 at 8:23 am

#262666

I am trying to eliminate every possible source of an error, which we cannot seem to reproduce consistantly?
Is this construct absolutely guaranteed to produce a consistent sequence of table variable TmpIDs every time? (assuming RealTable does not change)
DECLARE @Tmp TABLE( TmpID INT IDENTITY, TmpDATA VARCHAR);
INSERT INTO @Tmp (TmpDATA)
SELECT RealDATA
FROM dbo.RealTable
ORDER BY RealSort;
And would exactly the same behaviour be expected in SQL 2005?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

If it is critical to have TmpID in order by RealSort, you should use ROW_NUMBER()

DECLARE @Tmp TABLE( TmpID INT, TmpDATA VARCHAR);

INSERT INTO @Tmp (TmpID, TmpDATA)

SELECT

ROW_NUMBER() OVER(ORDER BY RealSort) as TmpID,

RealDATA

FROM

dbo.RealTable

ORDER BY

RealSort;

GSquared SSC Guru Points: 260824 More actions · Answer 2

Here's the answer from Microsoft: http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx

Relevant piece:

4.INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tom257 SSChampion Points: 11543 More actions · Answer 3

Tom257

SSChampion

Points: 11543

November 14, 2011 at 8:47 am

#1407236

I thought as much. Many thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Aside from identity, why does the order by of the insert matters?

If you want order in the subsequent selects, you HAVE TO specify ORDER BY in the select.

Tables have no order, so it makes no sense for inserts to have to honor an order (aside from identity).

GSquared SSC Guru Points: 260824 More actions · Answer 5

He asked about the ID sequence. That should do what's needed. ID can then be used in later Order By clauses to insure sequence.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GSquared (11/14/2011)
He asked about the ID sequence. That should do what's needed. ID can then be used in later Order By clauses to insure sequence.

Clustered PK on ID as well for a risky implicit sort ;-).

Since I use those tables once when I have 1 row in it it would work :hehe:.

Tom257 SSChampion Points: 11543 More actions · Answer 7

Ninja's_RGR'us (11/14/2011)
Aside from identity, why does the order by of the insert matters?
If you want order in the subsequent selects, you HAVE TO specify ORDER BY in the select.
Tables have no order, so it makes no sense for inserts to have to honor an order (aside from identity).

Its complicated. (isn't it always). But I've simplified the example to isolate the specific point in question. The actual insert happens in 3 chunks, with 3 different sort fields, and a developer has been lazy and instead of putting all 3 sort fields in the temp table, he's used this IDENTITY construct assuming it would give the correct sequence in the end. - Which it does on our QA machines - but not in production.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Tom Brown (11/14/2011)
Ninja's_RGR'us (11/14/2011)
Aside from identity, why does the order by of the insert matters?
If you want order in the subsequent selects, you HAVE TO specify ORDER BY in the select.
Tables have no order, so it makes no sense for inserts to have to honor an order (aside from identity).
Its complicated. (isn't it always). But I've simplified the example to isolate the specific point in question. The actual insert happens in 3 chunks, with 3 different sort fields, and a developer has been lazy and instead of putting all 3 sort fields in the temp table, he's used this IDENTITY construct assuming it would give the correct sequence in the end. - Which it does on our QA machines - but not in production.

No order by in the final select?