A payload arrives as printable characters, but the application expects the original bytes. SQL Server 2025 adds BASE64_ENCODE and BASE64_DECODE to make that conversion direct in T-SQL.
Begin BASE64_ENCODE With Bytes You Can Inspect
BASE64_ENCODE represents binary content using a limited character alphabet. That makes bytes easier to carry through text-oriented interfaces. SQL Server 2025's encoder takes varbinary and returns varchar. Its decoder takes the encoded varchar and returns varbinary. Keep those types explicit when you test a conversion.
I start with a short hexadecimal value before adding text conversions. It gives us an exact byte sequence to compare after decoding. Hexadecimal notation in T-SQL describes bytes directly. The example does not read a file, contact an API, or depend on a particular text encoding.
DECLARE @Bytes varbinary(8) = 0x0010FBEFFF;
DECLARE @Encoded varchar(max) = BASE64_ENCODE(@Bytes);
SELECT @Bytes AS OriginalBytes, @Encoded AS EncodedText,
BASE64_DECODE(@Encoded) AS DecodedBytes,
CASE WHEN BASE64_DECODE(@Encoded) = @Bytes
THEN 1 ELSE 0 END AS BytesMatch;The comparison checks the round trip rather than relying on the printed appearance. Use the returned bytes as the final authority. A string that looks reasonable is weak evidence when one misplaced character changes the content. Printable data has an excellent talent for looking respectable even when the recovered bytes are wrong.
Pick the BASE64_ENCODE Alphabet Explicitly
Standard Base64 uses characters that need care in some URL contexts. The encoder's second argument requests URL-safe output when set to one. That output substitutes the URL-safe alphabet and omits padding. The receiving interface still defines which representation it accepts.
DECLARE @Bytes varbinary(8) = 0xFBEFFFFA;
SELECT BASE64_ENCODE(@Bytes, 0) AS StandardText,
BASE64_ENCODE(@Bytes, 1) AS UrlSafeText;
SELECT BASE64_DECODE(BASE64_ENCODE(@Bytes, 0)) AS StandardRoundTrip,
BASE64_DECODE(BASE64_ENCODE(@Bytes, 1)) AS UrlSafeRoundTrip;BASE64_DECODE accepts both standard and URL-safe alphabets. That does not mean every other decoder in your integration does. SQL Server's XML and JSON Base64 decoders do not accept the encoder's URL-safe output. OPENJSON raises a conversion error for it, and the XML expression shown later returns NULL. Test the actual receiving path before switching an established interface.
URL-safe Base64 also does not replace every part of URL construction. Parameter names, separators, and other values retain their own formatting rules. Keep the byte encoding decision separate from how the application builds a complete request. This post only handles the payload's binary-to-text representation.
Convert Text Into Defined Bytes First
Text requires an additional contract: which bytes represent its characters? Converting nvarchar to varbinary preserves SQL Server's Unicode byte representation. That is suitable for a round trip back to nvarchar using the same representation. It is different from sending UTF-8 bytes to an external interface.
DECLARE @Text nvarchar(100) = N'Hello, SQL Server';
DECLARE @TextBytes varbinary(max) = CONVERT(varbinary(max), @Text);
DECLARE @Encoded varchar(max) = BASE64_ENCODE(@TextBytes);
SELECT @Encoded AS EncodedUnicodeBytes,
CONVERT(nvarchar(100), BASE64_DECODE(@Encoded)) AS RestoredText;Do not decode arbitrary external text bytes into nvarchar and assume they match. Inspect the interface's character encoding. SQL Server collation and storage type affect conversions involving varchar. An ASCII-only test can hide those differences because it exercises such a small part of the character set.
Demonstrate a UTF-8 Contract Deliberately
For a UTF-8 interface, use a varchar expression with a UTF-8 collation before converting to varbinary. The following example starts from Unicode text containing a non-ASCII character. The collation names the intended representation explicitly instead of leaving it to the database default.
DECLARE @Text nvarchar(100) = N'caf' + NCHAR(233);
DECLARE @Utf8Bytes varbinary(max) = CONVERT(varbinary(max),
CONVERT(varchar(max), @Text COLLATE Latin1_General_100_CI_AS_SC_UTF8));
DECLARE @Encoded varchar(max) = BASE64_ENCODE(@Utf8Bytes);
SELECT @Utf8Bytes AS Utf8Bytes, @Encoded AS EncodedUtf8,
CONVERT(nvarchar(max),
CONVERT(varchar(max), BASE64_DECODE(@Encoded))
COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS RestoredText;This example makes the byte and character steps visible. Repeat it with the characters your interface actually accepts. Include accented text and supplementary Unicode characters. Also test length limits. A truncated varchar or varbinary input cannot be repaired by the encoder that receives it.
Check the original character length and the encoded byte length separately. Different encodings use different numbers of bytes for the same characters. Keep that relationship visible when diagnosing a failed import. A successful decode confirms the Base64 structure, but it does not prove that the recovered bytes use the character encoding your application expects. Verify that final conversion too.
Compare the Older XML Expression
Earlier T-SQL code used FOR XML with BINARY BASE64 or an XML value expression. Those approaches remain useful when maintaining an older engine. Compare the same bytes rather than two differently converted strings. The following query shows the standard encoder beside the older XML serialization method. XML methods need SET QUOTED_IDENTIFIER ON, which SSMS sets by default; in sqlcmd, add the -I switch.
DECLARE @Bytes varbinary(max) = 0x0010FBEFFF;
SELECT BASE64_ENCODE(@Bytes) AS NativeEncoding,
(SELECT @Bytes
FOR XML PATH(''), BINARY BASE64) AS XmlEncoding;
DECLARE @Xml xml = N'';
DECLARE @Encoded varchar(max) = BASE64_ENCODE(@Bytes);
SELECT @Xml.value(
'xs:base64Binary(sql:variable("@Encoded"))',
'varbinary(max)') AS XmlDecodedBytes;The new functions state the conversion directly without adding XML machinery. Verify legacy expression output before replacing it in a stored module. Aliases, XML element wrappers, and NULL handling can change the surrounding shape. Match the complete interface contract, rather than replacing an expression because its name looks related.
Test Invalid Inputs and Missing Values
Invalid encoded content raises an error. The first query below fails on purpose, and its CATCH block shows error 9803. Catch it where your import process decides whether to reject a row, record a problem, or stop a batch. Do not quietly replace an undecodable payload with empty bytes. That turns damaged input into apparently valid content.
BEGIN TRY
SELECT BASE64_DECODE('%%%') AS DecodedBytes;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT BASE64_ENCODE(CAST(NULL AS varbinary(max))) AS NullEncoded,
BASE64_DECODE(CAST(NULL AS varchar(max))) AS NullDecoded;Both functions preserve NULL inputs. Test empty content separately because missing and empty values can represent different business states. The decoder tolerates certain whitespace characters, but malformed alphabet or padding still requires validation. Keep the raw incoming value available for an authorized diagnostic review.
Size the Destination for BASE64_ENCODE Output
Base64 adds representation overhead. Standard encoding uses four characters for each three-byte group, plus required padding. Use varchar(max) and varbinary(max) deliberately for large payloads. A small destination variable or column can truncate data before the round-trip check gets a chance to help.
I compare byte lengths alongside round-trip equality when changing an interface. Also check the consuming application's message limits. SQL Server's ability to return a long encoded string does not expand an external interface's payload allowance. Avoid encoding large file content repeatedly in a frequently executed query without measuring that workload.
Protect the Content Independently
Who can decode the value once it leaves your database? Anyone with a compatible decoder can recover the original bytes. Encoding provides no secret key and no confidentiality. Apply encryption, authorization, and transport protection according to the sensitivity of the underlying content.
Use BASE64_ENCODE when an interface requires binary content in text form. Keep the alphabet, character encoding, and length limits explicit. Check decoding against the original bytes. Those small tests prevent a surprising number of integration errors without pretending that an encoded string has become protected information.
Related reading on this blog: UTF-8 Collations in SQL Server 2019: When They Save Space and SQL SERVER 2019: Still Getting String or Binary Data Would be Truncated.
Base64 is not encryption, it is a printable representation of bytes.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
