Which SQL data type to store Base64 encoded file?

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.

  • How to prevent duplicate record inserts with PDO
  • Sane/fast method to pass variable parameter lists to SqlServer2008 stored procedure
  • Best practice for SQL Server 2008 schema change
  • Convert INT to BIT
  • Prevent database file from copying
  • Get next 2 values for sequence in single row
  • 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.

    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

  • Retrieve varbinary value as BASE64 in MSSQL
  • Is it a good practice to save a base64 string on the Database?
  • How to send base64 image to server - PHP
  • PHP Byte Array to Base64 (Byte Array From Mssql)
  • SQL Server base64 encoding stored function
  • One Solution collect form web for “Which SQL data type to store Base64 encoded file?”

    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.

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