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 means a Varbinary.)

  • Merge data in two row into one
  • PHP MSSQL mssql_fetch_array - can't get results to echo
  • SQL Update a table, use Case expression, then sum a column to get the total
  • How to get running sum of a column in sql server
  • How to determine if insert or update
  • How to change date value format to YYYYMMDD format
  • 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)?

  • Is there a way to expand the column list in a SELECT * from #Temp_Table in SSMS?
  • SQL Server - What happens when a row in a table is updated?
  • Select last records only if a certain condition is met
  • How to clean change tracking to specific version?
  • SQL data error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  • Cannot create login in stored procedure with EXECUTE AS OWNER?
  • 2 Solutions collect form web for “SQL Query to store text data in a Varbinary(max)”

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.