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

select convert(varchar(max), field , 1) 
from table

By using varchar(max) you won't have to worry about specifying the size (kind of).

If you were on Sql Server 2005 you could use this:

print master.sys.fn_varbintohexstr(@binvalue)

I don't think that exists on 2000, though, so you might have to roll your own.

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.