nvarchar(max), varchar(max), or should I really decode the string and put it in an image or blob or something?
nvarchar(max) varchar(max)还是应该对字符串进行解码并将其放入图像或blob中?
The longer version : (with all the juicy details)
I've got a .Net SOAP web service, which saves records in a SQL 2008 DB. The service is going to be extended to accept an image, which (for better or worse) also needs to go into the DB temporarily.
我有一个。net SOAP web服务，它在SQL 2008 DB中保存记录。服务将被扩展以接受一个映像，这个映像(无论好坏)也需要暂时进入DB。
To keep things simple, the service takes the image as a Base 64 encoded string, and will have to give it back as a base64 encoded string later (a different method on the same service).
为了保持简单，服务将图像作为一个Base 64编码的字符串，并必须稍后将其作为base64编码的字符串返回(同一服务上的另一种方法)。
I was originally just going to use nvarchar(max), and I'm sure this would work. But then I thought that base64 encoded means it could use varchar(max) instead, and use less storage space. Is this right? Or should I bite the bullet and decode the text to binary, store it as a blob, and then re-encode it on the way out again?
我本来打算用nvarchar(max)，我相信这能行。但是我认为base64编码意味着它可以使用varchar(max)，并且使用更少的存储空间。这是正确的吗?或者我应该咬紧牙关把文本解码成二进制，把它存储为一个blob，然后在输出的时候重新编码?
Finally - load storage and performance are unlikely to cause problems, this is a pet project and will be low load.
最后，负载存储和性能不太可能导致问题，这是一个pet项目，将是低负载。
Edit: In response to @howiecamp's question, I wrote up how I was URL encoding the image here: http://www.flowerchild.org.uk/archive/2010/06/13/base-64-encoding-an-image-to-pass-across-a-web.html
编辑:在回答@howiecamp的问题时，我在这里写下了如何编码图像的URL: http://www.flowerchild.org.uk/archive/2010/06/13/base-64-encoding- image-to-pass-across- web.html
1 个解决方案
#1
62
Well, Base64 is a ASCII encoding, really - so definitely no need for NVARCHAR - and since it's text, I'd suggest VARCHAR(MAX)
Base64是一种ASCII编码，所以绝对不需要NVARCHAR由于它是文本，我建议使用VARCHAR(MAX)
It's pure text, up to 2 GB (should be enough), and it's a string-type, so you can use all string functions on it. NVARCHAR does indeed use twice as much storage - always 2 bytes per character - and is totally unnecessary in this case.
它是纯文本，最多2gb(应该足够了)，而且是字符串类型，所以您可以在上面使用所有的字符串函数。NVARCHAR确实使用了两倍的存储空间——每个字符都是2个字节——在这种情况下完全没有必要。
#1
62
Well, Base64 is a ASCII encoding, really - so definitely no need for NVARCHAR - and since it's text, I'd suggest VARCHAR(MAX)
Base64是一种ASCII编码，所以绝对不需要NVARCHAR由于它是文本，我建议使用VARCHAR(MAX)
It's pure text, up to 2 GB (should be enough), and it's a string-type, so you can use all string functions on it. NVARCHAR does indeed use twice as much storage - always 2 bytes per character - and is totally unnecessary in this case.
它是纯文本，最多2gb(应该足够了)，而且是字符串类型，所以您可以在上面使用所有的字符串函数。NVARCHAR确实使用了两倍的存储空间——每个字符都是2个字节——在这种情况下完全没有必要。