In SQL Server 2008 The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values

SQL Server 2008 CTP 6 has enhanced the convert function when you do conversion between binary and character hexadecimal valuesThere 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

SELECTCONVERT(varbinary(5),'denis')

The output will be 0x64656E6973. Now we can use that in the select statements below

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 :-(

I don't have CTP6 loaded yet, but I ran your samples on CTP5. They work as you describe for styles 0 and 1 but not style 2 (so just style 2 is new to CTP6). I noted that other higher number styles are accepted by both SQL 2005 and SQL 2008 CTP5 (tested styles 3 and 4; does not produce errors of "invalid style" or similar error), but just returns the text result of "denis" as you had stated for style 0.

Leave a Comment

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.