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.

  • SQL Server Nvarchar and Java prepared statement
  • Insert data into columns of Excel sheet from SQL Server table
  • SSIS Execute SQL Stored Procedure output parameter type mismatch
  • Import / Export Data Problem with Sql Server Management Studio
  • Overriding chocolateyInstall.ps1 script with Ansible
  • How can I get certain rows and the “previous” rows from a table that includes a datetime column?
  • 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

  • Is it a good practice to save a base64 string on the Database?
  • SQL Server base64 encoding stored function
  • Retrieve varbinary value as BASE64 in MSSQL
  • PHP Byte Array to Base64 (Byte Array From Mssql)
  • (PHP) Insert a base64_decoded string to an image column sql server
  • How to send base64 image to server - PHP
  • 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.