If you intend to store your Base64 string as is, you can use the VARCHAR
data type. The Base64 encoding has been devised to use only 7-bit ASCII characters.
However, if you prefer to store your data in binary format, you would need to use the VARBINARY
data type and convert your Base64 string to binary. You can use the XQuery functionality (SQL Server 2005 onwards) to easily convert values to VARBINARY
and vice-versa.
Convert Base64 value in a variable to VARBINARY
:
declare @str varchar(20);
set @str = '3qAAAA==';
select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)');
Convert binary value in a variable to 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 more examples): Converting from Base64 to varbinary and vice versa.