Sql Query To Store Text Data In A Varbinary(max)

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 me

Solution 1:

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 tabledeclare@testtable (
    data varbinary(max) notnull,
    datatype varchar(10) notnull
)

-- insert varcharinsertinto@test (data, datatype) selectcast('asdf'asvarbinary(max)), 'varchar'-- insert nvarcharinsertinto@test (data, datatype) selectcast(N'asdf'asvarbinary(max)), 'nvarchar'-- see the resultsselect data, datatype from@testselectcast(data asvarchar(max)) as data_to_varchar, datatype from@testselectcast(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.

Solution 2:

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.