Select records from a specific key onwards

Question

I have a table that has more than three trillion records
The main key of this table is guid

As below

               GUID                       Value   mid  id  
   0B821574-8E85-4FB7-8047-553393E385CB    4    51   15 
   716F74B0-80D8-4869-86B4-99FF9EB10561    0    510  153 
   7EBA2C31-FFC8-4071-B11A-9E2B7ED16B2B    2    5    3 
   85491F90-E4C6-4030-B1E5-B9CA36238AE2    1    58   7  
   F04FA30C-0C35-4B9F-A01C-708C0189815D    20   50  13

guid is primary key I want to select 10 records from where the key is equal to, for example, 85491F90-E4C6-4030-B1E5-B9CA36238AE2

Hello I want to read 10 to 10 table records and send them to another server only via udp port — user14744285, Dec 01 '20 at 21:22
Your row numbering at the left side of the data is what confusing you. There's no "the first" or "the last" row in database table, the correct way to guarantee row order in SQL result set is to explicitly specify `ORDER BY`. So please, clarify what rows do you need to read. — astentx, Dec 01 '20 at 23:36
ok . I explained above that I want to send this table to another server via the udp port — user14744285, Dec 02 '20 at 07:37

score 0 · Answer 1 · answered Dec 01 '20 at 20:17

0

You can use order by and top. Assuming that guid defines the ordering of the rows:

select top (10) t.*
from mytable t
where guid >= '85491F90-E4C6-4030-B1E5-B9CA36238AE2'
order by guid

If the ordering is defined in an other column, say id (that should be unique as well), then you would use a correlated subquery for filterig:

select top (10) t.*
from mytable t
where id >= (select id from mytable t1 where guid = '85491F90-E4C6-4030-B1E5-B9CA36238AE2')
order by id

answered Dec 01 '20 at 20:17

GMB

216,147
25
84
135

I used this method. But it is very slow – user14744285 Dec 01 '20 at 21:12
@user14744285: are you using the first query or the second query? – GMB Dec 01 '20 at 21:13
first query Not my answer . I using second query I explained above . I want to send this table to another server via the udp port – user14744285 Dec 02 '20 at 07:40

Raimonds Rubiķis · Answer 2 · 2020-12-02T10:31:01.640

To read data onward You can use OFFSET .. FETCH in the ORDER BY since MS SQL Server 2012. According learn.microsoft.com something like this:

-- Declare and set the variables for the OFFSET and FETCH values.  
 DECLARE @StartingRowNumber INT = 1  
       , @RowCountPerPage INT = 10;  
 -- Create the condition to stop the transaction after all rows have been returned:  
 WHILE (SELECT COUNT(*) FROM mytable) >= @StartingRowNumber  
 BEGIN  
 -- Run the query until the stop condition is met:  
 SELECT *
 FROM mytable WHERE guid = '85491F90-E4C6-4030-B1E5-B9CA36238AE2'
 ORDER BY id   
     OFFSET @StartingRowNumber - 1 ROWS   
     FETCH NEXT @RowCountPerPage ROWS ONLY;  
 -- Increment @StartingRowNumber value:  
 SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;  
 CONTINUE  
 END;

In the real world it will not be enough, because another processes could (try) read or write data in your table at the same time. Please, read documentation, for example, search for "Running multiple queries in a single transaction" in the https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql
Proper indexes for fields id and guid must to be created/applied to provide performance

Select records from a specific key onwards

2 Answers2