How to get data in chunks from very large table which has primary key column data type as varchar

Question

I want to retrieve data from table with 28 million rows. I want to retrieve 1 million rows at a time.

I have checked answers from following links Get data from large table in chunks How can I get a specific chunk of results? The solution suggests a query which has int ID column. In my case I have primary key column with varchar(15) as data type

I want to use something like this which is faster - Select top 2000 * from t where ID >= @start_index order by ID

But since the ID column is varchar, I can not use integer index.

How can I efficiently get data in chunks from a table with primary key having varchar data type?

Database is read only. I can not make any changes in database — sam, Jul 08 '16 at 13:56
Eventhough your ID is Varchar what kind of values exist in your Primary Key column. Either alphanumeric or numeric data — StackUser, Jul 08 '16 at 13:57
ID contains alphanumeric data and also contains characters like $ and _ — sam, Jul 08 '16 at 13:59
You want to retrieve 1 million data among 28 million based on what criteria — StackUser, Jul 08 '16 at 14:01
There's no problem with ordering or useing `>=` but SQL Server 2012 provides the `[OFFSET and FETCH NEXT](http://sqlmag.com/sql-server-2012/using-new-offset-and-fetch-next-options) statements which make paging much easier. That said, a page of 2000 records is not a good idea. — Panagiotis Kanavos, Jul 08 '16 at 14:05
Perhaps you should describe your *real* problem? The fastes way to export 1M rows is to read the data in a firehose fashion, eg with an SSIS package that exports it directly where it needs to go. Or use `bcp` to perform a bulk export operation. *Partitioning* the table can make processing faster if you only want to process the records in one partition — Panagiotis Kanavos, Jul 08 '16 at 14:08

score 3 · Accepted Answer · answered Jul 08 '16 at 13:55

3

Because your primary key has to be unique the same approach will work. You can use >= with character columns.

answered Jul 08 '16 at 13:55

Joe C

score 2 · Answer 2 · answered Jul 08 '16 at 14:12

2

I'll suggest to use an offset ORDER BY like this:

SELECT * 
FROM t
ORDER BY ID
OFFSET 1000 ROWS FETCH NEXT 500 ROWS ONLY;

This way is very flexible.

answered Jul 08 '16 at 14:12

Kilren

2 Answers2