Which SQL data type to store Base64 encoded file?

Question

nvarchar(max), varchar(max), or should I really decode the string and put it in an image or blob or something?

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.

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

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?

Finally - load storage and performance are unlikely to cause problems, this is a pet project and will be low load.

Answer 1

Well, Base64 is a ASCII encoding, really - so definitely no need for NVARCHAR - and since it's text, I'd suggest 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.