Insert the base 64 row into the SQL Server database

Question

I get a base 64 row from an XML file that I want to insert into a SQL Server database. What type of field does a field have in my database? VARBINARY (MAX)? Do I have to convert base line 64 to a different format before pasting into my database?

Regards

Answer 1

Since a string and a string are a string, this is a string, you can easily put it in the VARCHAR(x) or VARCHAR(MAX) field.

The "size" of VARCHAR(x) has a max. 8000 characters long, and VARCHAR(MAX) 2 GB (2 ** 31-1 bytes) in size.

Answer 2

If you intend to keep the Base64 string as is, you can use the VARCHAR data type. Base64 encoding was designed to use only 7-bit ASCII characters.

However, if you prefer to store your data in binary format, you will need to use the VARBINARY data VARBINARY and convert the Base64 string to binary. You can use the XQuery functionality (starting with SQL Server 2005) to easily convert values ​​to VARBINARY and vice versa.

Converting a Base64 value to a variable in VARBINARY :

 declare @str varchar(20); set @str = '3qAAAA=='; select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)'); 

Converting a binary value to a variable in Base64:

 declare @bin varbinary(20); set @bin = 0xDEA00000; select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(20)'); 

Source (and other examples): Converting from Base64 to varbinary and vice versa .

Answer 3

It is worth noting that if you use varchar(x) or varchar(max) with base64 strings and use base64 string in any WHERE clauses to search, you should use case-sensitive sorting in the column, because case has a value with base64 encoded data.

Answer 4

varbinary (MAX) will be the most efficient storage medium (raw bytes are less than encoded b64), but you will have to convert from b64 to raw bytes. If you want to save b64 as you get it, just use varchar (max). In fact, if you are going to decompose it back into XML, there would be less processing to leave only b64.

Answer 5

You can either insert the Base64 row directly into the VARCHAR column, or you can convert the Base64 row to a byte[] array and save it in the VARBINARY column.

Determining which option is most appropriate will depend on what you subsequently need to do with the data. If you need a Base64 row, save it this way (in the VARCHAR column); If you need a byte[] array, then convert it (and save it in the VARBINARY column).

Answer 6

I always suggest that you store the image as varbinary (max) in DB, because Varbinary is a performance incentive and it takes up very little memory compared to varchar (max), but with modern databases and storage, which, probably a lot less concern. If you want to save byte [], go for varbinary (max). If you want to keep the base64 string, I always suggest nvarchar (max). However, performance and size will always be expensive.