Is there any functionality that allows reading binary file in chunks?
Currenty I am using the following query to read binary files:
SELECT image_data.BulkColumn FROM OPENROWSET(BULK ‘C:bigfile’, SINGLE_BLOB) as image_data
When size of the file is about 3 GB, the query takes about 50 seconds to execute.
When I am trying to extract a chunk from varbinary:
SELECT SUBSTRING(image_data.BulkColumn, 1, 15) FROM OPENROWSET(BULK ‘C:bigfile’, SINGLE_BLOB) as image_data
it takes about 28 seconds, no matter what the value of substring-offset is.
Are there any other ways to make the file-to-sql gateway in a more effective way? I don’t want to store large binary data in the database.