SQL Server Varbinary(max): select a subset of bytes from the varbinary field

Question

What is the most efficient way of reading just part of the binary data from a varbinary(MAX) field (not using FileStreams) in SQL Server 2008?

When writing data to the column the VarBinary.Write() function is available in T-SQL, allowing bytes to be written to the field incrementally, but there doesn't appear to be a similar function available for reading data.

I know of the DataReader.GetBytes() method in .Net which will select just the bytes you ask for, but does this carry a performance overhead with it? i.e. will the select in sqlserver read all of the bytes in the database, and then give the getBytes() method all of these bytes for it to take the subset of bytes requested from them?

Thanks for any help.

Solution 1

You use SUBSTRING. This reads a snippet from your varbinary data on the server, and only returns the snippet to the client.

[SUBSTRING] Returns part of a character, binary, text, or image expression in SQL Server.. [it] Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.

Solution 2

Using DataReader.GetBytes() is possible without overhead, afaik. But you'll have to call the ExecuteReader() with the CommandBehavior.SequentialAccess option.