mysql varbinary 长度,SQL查询来存储文本数据的VARBINARY（最大值）

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)?

解决方案

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.