0

I am trying to select some varbinary data from TABLE(DATA varbinary(2048)) to my .NET dll without any padding. All my records are currently 64 bytes long but they may vary in the future.

I use a stored procedure that does this: 

 select substring(DATA, 1, datalength(DATA)) as DATA from TABLE

I would expect this to work but the stream I get in my dll is 2050(2048 + 2) bytes long.

When I hard code the value (select substring(DATA, 1, 64) as DATA from TABLE) it returns 66 bytes as I would expect.

Am I missing something (obvious)?

Bridge
  • 29,818
  • 9
  • 60
  • 82
Evan V.
  • 37
  • 1
  • 7

1 Answers1

1
select substring(DATA, 1, datalength(DATA)) as DATA from TABLE

This is a misunderstanding of SQL Server types. When you start off with a varbinary(2048) column, unless you make it longer, it will stay at 2048 and will not collapse even if you reduce the actual data content.

This shows you that the length of DATA in temptbl is still 2048, even though there is only one record in the source table and it is of length 64.

create table tbl(data varbinary(2048))
;
insert into tbl select convert(varbinary(2048),REPLICATE('a',64))
;
select substring(DATA, 1, datalength(Data)) as DATA
into temptbl
from tbl
;
exec sp_help temptbl

If you really wanted to, you would need to use dynamic SQL to size the output column, but there is very rarely any need to do such a thing.

declare @nsql nvarchar(max)
set @nsql = 'select convert(varbinary(' +
 right((select max(datalength(Data)) as MaxLen from tbl),12) +
 '), data) as Data from tbl'
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Sizing the output of the column is exactly what I am trying to do. My data is encrypted text which I need to send to my dll for decryption and therefore needs to be exactly the same size, any padding will corrupt the data. I'll try the dynamic SQL approach tomorrow. Thank you. – Evan V. Feb 01 '11 at 21:50
  • Bear in mind the entire column (definition) is of a specific size, so if you add 128bit data in the same column in the future, the column returned (sized) will be varbinary(128). I rather think you should calc the length of the data returned from the front-end, which should be trivial. Either that or pass the length in another column, i.e. `select datalength(data) as size, data from..` – RichardTheKiwi Feb 01 '11 at 22:13
  • May I just clarify one thing? `right(...,12)` is your way of converting to varchar in this case? Because it seems so. Is it beneficial in any way to plain old and commonplace `cast/convert` method? – Andriy M Feb 02 '11 at 14:21
  • @Andriy - yes it is. Just a shortcut notation which performance-wise has negligible difference to cast/convert – RichardTheKiwi Feb 02 '11 at 19:43
  • 1
    @cyberwiki: After some experimentation I gave up on sql and resorted to trancating the data in the front-end. Thanks again. – Evan V. Feb 03 '11 at 09:14