How can I print a binary value as hex in TSQL?

Do not use master.sys.fn_varbintohexstr – it is terribly slow, undocumented, unsupported, and might go away in a future version of SQL Server.

If you need to convert binary(16) to hex char, use convert:

convert(char(34), @binvalue, 1)

Why 34? because 16*2 + 2 = 34, that is “0x” – 2 symbols, plus 2 symbols for each char.

We tried to make 2 queries on a table with 200000 rows:

1. select master.sys.fn_varbintohexstr(field)
   from table`
   

2. select convert(char(34), field, 1)
   from table`
   

the first one runs 2 minutes, while second one – 4 seconds.