Essentially, you are asking if you can perform a single ordered scan through the data overall, while making no copies of the data, and returning 'x' disjoint sets of rows from the full set on each call. This is exactly the behaviour of an appropriately-configured API cursor.
For example, using the AdventureWorks table Person.EmailAddress
to return sets of 1,000 rows:
DECLARE
@cur integer,
-- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
@scrollopt integer = 16 | 8192 | 16384,
-- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
@ccopt integer = 1 | 32768 | 65536,
@rowcount integer = 1000,
@rc integer;
-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen
@cur OUTPUT,
N'
SELECT *
FROM AdventureWorks2012.Person.EmailAddress
WITH (INDEX([IX_EmailAddress_EmailAddress]))
ORDER BY EmailAddress;
',
@scrollopt OUTPUT,
@ccopt OUTPUT,
@rowcount OUTPUT;
IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
-- Name the cursor so we can use CURSOR_STATUS
EXECUTE sys.sp_cursoroption
@cur,
2,
'MyCursorName';
-- Until the cursor auto-closes
WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
BEGIN
EXECUTE sys.sp_cursorfetch
@cur,
2,
0,
1000;
END;
END;
Each fetch operation returns a maximum of 1,000 rows, remembering the position of the scan from the previous call.
Best Answer
#temp
is session scopeCREATE TABLE (Transact-SQL) - Temporary Tables
A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table.
Do the official microsoft way no tricks, it would end in a mess