Is an IDENTITY column auto-incremented before or after an order by clause is applied to it?

Question

I have a temporary table that has an identity column. There are multiple inserts into this table from several other tables. In one of the inserts, however, I need to insert rows into it in a specific order for which I am using an ORDER BY clause. Will the auto-increment be applied before or after the rows in the temporary table are sorted?

Thanks!

CREATE TABLE #tempTable
 (
    SeqNo INT IDENTITY(1, 1),
    Column1 INT,
    Column2 INT,
    Column3 INT
)

INSERT #tempTable
 (
    Column1,
    Column2,
    Column3
)
SELECT  W, 
        X, 
        Y
FROM    dummyTable
ORDER BY Z

I guess my question is whether SeqNo will be auto-incremented in a way such that the row with the smallest Z value gets 1, second smallest gets 2 and so on.

Apologies if the code example is a bit scruffy..

Answer 1

The SQL Server Engine Team have made this blog post:

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

They clarify what "the order in which the rows are inserted" means in the comments:

Yes, the identity values will be generated in the sequence established by the ORDER BY. If a clustered index exists on the identity column, then the values will be in the logical order of the index keys. This still doesn't guarantee physical order of insertion. Index maintenance is a different step and that could also be done in parallel for example. So you could end up generating the identity values based on ORDER BY clause and then feeding those rows to the clustered index insert operator which will perform the maintenance task.