0

Hi i have this piece of code,

ALTER PROCEDURE [dbo].[Proc0] AS BEGIN
CREATE TABLE #result (id int identity(1,1), data varchar(max))

--result sorted by data
exec proc1 --let say proc1 sort data on output 

DECLARE @sql varchar(max)
SELECT @sql = 'exec proc1 ' --returns only 1 column
SELECT @sql='INSERT INTO #result ' +@sql
EXEC(@sql)

SELECT * FROM #result order by id
END

Does the result set inserted into #result is in same order as produced by proc1? Or will two result set output be identical?

Nakul Manchanda
  • 115
  • 3
  • 11
  • 4
    What do you mean by inserted in the same order? SQL, by its very nature, is unsorted. The inserted order is irrelevant. – Siyual Mar 04 '15 at 18:21
  • Edited a code to include a identity column in #result table if result set produced by proc1 is a,b,c sorted by proc1 then if I put SELECT * FROM #result order by id at end of proc0. Is there a gurantee it display result as a,b,c? – Nakul Manchanda Mar 04 '15 at 21:21
  • 1
    I'd have thought it would be identical per number 4 in [Ordering guarantees in SQL Server...](http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx) but that page doesn't explicitly guarantee this scenario. – Martin Smith Mar 04 '15 at 21:29
  • You need to explicitly specify the order outside the stored procedure during the `INSERT`. It doesn't matter if stored procedure sorts anything or not. Quite elaborate method is to use [`OPENROWSET`](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) https://msdn.microsoft.com/en-AU/library/ms190312.aspx `INSERT INTO #result SELECT * FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC proc1') AS a ORDER BY a.Col1, a.Col2 ....` – Vladimir Baranov Mar 04 '15 at 23:09

1 Answers1

2

There is nothing that guarantees it is, so the safe and correct answer is "no, this is not guaranteed".

What will happen in practice is that if the insert is executed as a non-parallel statement, it is highly likely that the physical order of the rows will match the sort order of the stored procedure. But the even more interesting twist is, of course, that there is no way of telling that this is the case using any SELECT -- because any SELECT that has no ORDER BY does not guarantee the order itself, and any SELECT that does have an ORDER BY will sort the results regardless (it cannot know, and will not assume, that the rows are already in the desired order). If parallelism is involved and you have no ORDER BY, the results can be rearranged even if they should happen to be in the "correct" order in the table.

So with that I'll come back to where we started and say "no, this is not guaranteed". Whenever you want results in a particular order, use ORDER BY on retrieval. Worrying about the order on insertion is usually only relevant when you're doing bulk insert operations, and even then, it only matters for performance, not for the correctness of the results. If you find yourself writing any query where the order of a result is not completely enforced using ORDER BY, and you are counting on that anyway, you're doing it wrong.

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • 1
    They *do* have an order by on retrieval. `SELECT * FROM #result order by id` so the question is just whether or not it is guaranteed that the `IDENTITY` values are assigned in the same order as the proc returns them. – Martin Smith Mar 04 '15 at 22:07
  • @MartinSmith: Ah, I interpreted the comment "if result set produced by proc1 is a,b,c sorted by proc1" as a result set including unrelated columns that happened to be sorted on id (but not those other columns). Of course the question only has `id` and `data`. The main point stands, and I've revised the answer accordingly. – Jeroen Mostert Mar 04 '15 at 22:10
  • 2
    Well they are asking two different questions really. (Q1) Will it be inserted in the same order? (A1) Who cares. Insertion order is irrelevant. (Q2) will the two result sets output be identical? (A2) Yes - If the stored proc returns rows in deterministic order and that is the same order that the `identity` is assigned. It would be guaranteed if they did a `INSERT ... SELECT ... ORDER BY` that the identity is assigned in that order. Not sure if wrapping the `SELECT ... ORDER BY` in a proc would alter that. – Martin Smith Mar 04 '15 at 22:21
  • 3
    @MartinSmith: you're right, and I'm only answering #1. The answer to #2 is in fact much more interesting. The page you linked doesn't answer it. I have a suspicion the answer might be "no", since I see no reason why `INSERT .. EXEC` should take particular pains to respect a potential order in the result of the `EXEC`, but only an insider could definitely confirm. I'm leaving my answer up because it adds something, but it's definitely incomplete. – Jeroen Mostert Mar 04 '15 at 22:26
  • I read you answer, you saying "No", thats what my experience been working with large dataset unless I find some other issue. I need this procedure to insert in right order, ordering is important because i use the result set to produce a compliant file which needs ordering. using order by on #result was my best guess. Parent procedure is kind of general procedure which can call various exports by name. Thanks @MartinSmith for simplifying explanation of a issue. – Nakul Manchanda Mar 04 '15 at 22:39
  • @NakulManchanda: if the internal sproc can be rewritten as a table-valued function (no ordering) you can use `INSERT ... SELECT FROM dbo.f(...) ORDER BY` with an identity. If this is not possible, you are probably required to store the intermediate results (in whatever order) and order them definitively for the final result. You could circumvent *that* if you had a client consume the result and produce the files -- a client can use the fact that the result set is ordered, even if `INSERT ... EXEC` could not. – Jeroen Mostert Mar 04 '15 at 22:50
  • Thanks @JeroenMostert thats what I did as a workaround SELECT * FROM #result order by data --instead of id but it breaks the generality of proc0/general purpose procedure, was hoping to look for more alternatives. – Nakul Manchanda Mar 04 '15 at 22:55