Get result and ids variable in 1 SELECT statement

Question

I have a complex select statement which I want to return to the application layer. At the same time I want to reuse ids from this statement. I've come to the table variable solution like this

DECLARE @Result TABLE (SortOrder int IDENTITY, Id bigint, data_columns, PRIMARY KEY (Id))
INSERT INTO @Result
SELECT Id, data_columns ...

--output to the app layer
SELECT Id, data_columns FROM @Result ORDER BY SortOrder;

--using @Result Id elsewhere

SortOrder is used to keep original order from the first select. Without it I faced a strange bug in ms sql server 2012. PRIMARY KEY (Id) is used as a hint to optimizer that Id values are unique. SortOrder does not correlate with Id.

Now I have concerns about memory and cpu overload. Does anyone have a better solution?

----- Addition: This can be an answer

DECLARE @Ids TABLE (Id bigint, PRIMARY KEY (Id));

MERGE @Ids AS ids
USING (SELECT TOP 9223372036854775807 Id, data_columns ...) AS result
ON 1=0
WHEN NOT MATCHED THEN
INSERT VALUES(result.Id)
OUTPUT result.*; --output to the app layer here

--using @Ids Id elsewhere

I can confirm it works on my sample and takes less time. But I'm still worried about order in the OUTPUT.

TOP hack explained MERGE INTO insertion order

MSDN also says

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

Luckily I don't care about order in @Ids table. But it's important to get the same order in OUTPUT clause as it was in USING ()

1. Size of table variable. I don't need to store data_columns after output — svolkov, Dec 21 '16 at 12:15
There is not enough information for anyone to help you. What do you mean by "Size of Table"? — Neo, Dec 21 '16 at 12:16
Also what strange bug did you face? I'm pretty sure if it's just the order you get the data in it's not a bug at all. — Sami Kuhmonen, Dec 21 '16 at 12:17
It could be 100 data columns which I have to copy into table variable together with Id column — svolkov, Dec 21 '16 at 12:23
Bug explained http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause — svolkov, Dec 21 '16 at 12:30
That is not a bug, as the answer explains. Why do you need a table variable? Why can't you just do `SELECT Id, data_columns .. ORDER BY Id;` using the first SELECT? — ADyson, Dec 21 '16 at 13:23

score 0 · Answer 1 · edited May 23 '17 at 11:59

It looks to me like you are trying to persist a particular sort order in which case you can use ROW_NUMBER to produce the desired effect:

  SELECT ROW_NUMBER() OVER(ORDER BY OriginalSortingColumns) AS SortOrder                  
       , data_columns 
    FROM sourcetable

But without knowing more about the purpose of your program and about the structure and nature of your data I can't be sure. It's well worth reading this guide - How do I ask a good question? - to make sure you provide all the info we need to get you a great answer quickly.

As the comments say you can never rely on keys to order the data, that is what the ORDER BYstatement is for.

Thank you for reply. I'm asking a technical question. I believe that providing details in SELECT statement instead of three dots will give me partial answers. I made another addition to my question — svolkov, Dec 21 '16 at 17:50

Get result and ids variable in 1 SELECT statement

1 Answers1