Split query result by half in TSQL (obtain 2 resultsets/tables)

Question

I have a query that returns a large number of heavy rows.

When I transform this rows in a list of CustomObject I have a big memory peak, and this transformation is made by a custom dotnet framework that I can't modify.

I need to retrieve a less number of rows to do "the transform" in two passes and then avoid the memory peak.

How can I split the result of a query by half? I need to do it in DB layer. I thing to do a "Top count(*)/2" but how to get the other half?

Thank you!

Answer 1

Here is another way to do it from(http://www.tek-tips.com/viewthread.cfm?qid=1280248&page=5). I think it's more efficient:

Declare @Rows Int
Declare @TopRows Int
Declare @BottomRows Int

Select @Rows = Count(*) From TableName

If @Rows % 2 = 1
  Begin    
    Set @TopRows = @Rows / 2
    Set @BottomRows = @TopRows + 1
  End
Else
  Begin
    Set @TopRows = @Rows / 2
    Set @BottomRows = @TopRows
  End    

Set RowCount @TopRows

Select * From TableName Order By DisplayOrder

Set RowCount @BottomRows

Select * From TableNameOrder By DisplayOrderDESC

--- old answer below ---
Is this a stored procedure call or dynamic sql? Can you use temp tables?

if so, something like this would work

select row_number() OVER(order by yourorderfield) as rowNumber, * 
  INTO #tmp
FROM dbo.yourtable

declare @rowCount int
SELECT @rowCount = count(1) from #tmp

SELECT * from #tmp where rowNumber <= @rowCount / 2

SELECT * from #tmp where rowNumber > @rowCount / 2

DROP TABLE #tmp

Answer 2

SELECT TOP 50 PERCENT WITH TIES ... ORDER BY SomeThing

then

SELECT TOP 50 PERCENT ... ORDER BY SomeThing DESC

However, unless you snapshot the data first, a row in the middle may slip through or be processed twice

Answer 3

If you have identity field in the table, select first even ids, then odd ones.

select * from Table where Id % 2 = 0

select * from Table where Id % 2 = 1

You should have roughly 50% rows in each set.

Answer 4

I don't think you should do that in SQL, unless you will always have a possibility to have the same record 2 times.

I would do it in an "software" programming language, not SQL. Java, .NET, C++, etc...