SQL Server 2008 CTP 6 has enhanced the convert function when you do conversion between binary and character hexadecimal values
There are 3 styles:
Style 0 works the same as on 2005 it converts binary to varchar, if you have 0x64656E6973 then you will get 'denis'
Style 1 converts binary to varchar but the values stay the same, if you have 0x64656E6973 you will get '0x64656E6973'
Style 2 strips the 0x but leaves the rest of the values, if you have 0x64656E6973 you will get '64656E6973'
Try it out
When you run this
The output will be this 0x64656E6973. Now we can use that in the select statements below
SELECT CONVERT(varchar(18), 0x64656E6973, 0) AS 'Style 0' --denis
SELECT CONVERT(varchar(18), 0x64656E6973, 1) AS 'Style 1' --0x64656E6973
SELECT CONVERT(varchar(18), 0x64656E6973, 2) AS 'Style 2' --64656E6973
When you run the code above on SQL Server 2005 all 3 select statements return 'denis'
It is all in Books On Line for CTP 6, it would have been nice if they also included the sys.dm_tran_commit_table dmv or the sys.dm_exec_trigger_stats dmv. I have no clue what the sys.dm_tran_commit_table dmv is supposed to do :-(