5

Is there a way to make a varbinary accept text data in SQL Server?

Here is my situation. I have a fairly large amount of XML that I plan on storing in a "zipped" format. (This means a Varbinary.)

However, when I am debugging, I want to be able to flip a configuration switch and store in plain text so I can troubleshoot from the database (ie no client app to un-zip needed).

Is it possible to insert normal text in to a varbinary(max)?

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
Vaccano
  • 78,325
  • 149
  • 468
  • 850
  • 1
    `select cast('asdf' as varbinary(max))` `select cast(cast('asdf' as varbinary(max)) as varchar(30))` – Tim Lehner Jan 30 '12 at 16:51
  • Storing objects that are bigger from 2 MB in the SQL server table is a bad technique and it is always (in my practice) connected with future problems. I recommend to you, if you are going to store bigger objects to read about "FileStream Enabled Database" - shortly: you are using varbinary(max) but no limit for the size of the file and it is stored on your hard disk. – gotqn Jan 30 '12 at 18:03
  • @Joro - Thanks for the comment. I looked into FileStream previously. But one column of datatype FileStream makes it so you cannot mirror your database. That is not a feature I am willing to give up. – Vaccano Jan 30 '12 at 23:26

2 Answers2

4

Is it possible to insert normal text in to a varbinary(max)?

Yes, just be sure of what you are storing so you know how to get it back out. This may shed some light on that:

-- setup test table
declare @test table (
    data varbinary(max) not null,
    datatype varchar(10) not null
)

-- insert varchar
insert into @test (data, datatype) select cast('asdf' as varbinary(max)), 'varchar'
-- insert nvarchar
insert into @test (data, datatype) select cast(N'asdf' as varbinary(max)), 'nvarchar'

-- see the results
select data, datatype from @test
select cast(data as varchar(max)) as data_to_varchar, datatype from @test
select cast(data as nvarchar(max)) as data_to_nvarchar, datatype from @test

UPDATE: All of this assumes, of course, that you don't want to utilize the expressive power of SQL Server's native XML datatype. The XML datatype also seems to store its contents fairly efficiently. In my database I regularly see that it's as little as half the size of an equal string of varchar, according to datalength(). This may not be all that scientific, and of course, YMMV.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • >>as little as half the size of an equal string<< If that was the case for me I would not compress my data. However, I dumped an example XML string into two test tables and the XML data type used 2,055 bytes and the varchar(max) used 2,135 (only 80 bytes difference). Is there some kind of configuration I need to do to make it work? – Vaccano Jan 30 '12 at 18:04
  • This may have a lot to do with the amount of whitespace in my original texts that would be removed as it's converted to native XML: http://msdn.microsoft.com/en-us/library/ms190965.aspx – Tim Lehner Jan 30 '12 at 20:17
  • You may also find some interesting ideas here: http://stackoverflow.com/questions/1089150/net-compression-of-xml-to-store-in-sql-server-database – Tim Lehner Jan 30 '12 at 20:37
1

You can use this answer to convert your string to a byte array, and insert the result into a varbinary(max) column. The idea is to use BinaryFormatter with a MemoryStream to serialize the string, harvest the resulting byte array from the memory stream, and write it into a varbinary(max) column.

Community
  • 1
  • 1
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523