Not really surprised as the implicit conversion truncates the data.  What is interesting is that inserting the longer value works as well through truncating, but if you try the same thing with char or varchar you get string or binary data would be truncated error unless you do an explicit conversion.

Hi Denis,

There's a vast difference between the examples in the post and in your comment. In the first query in the post, you compare uniqueidentifier to char(47); based on the rules of data type precedence (see BOL), char gets converted to uniqueidentifier because the latter has higher precedence. It's equally unsurprising that the string is truncated during the conversion, as this is clearly documented in BOL (in the article on CONVERT).

The query in your comment, though, compares varchar(4) to char(11). The rules of data type precedence force a conversion from char(11) to varchar(11). After that, comparison between two varchar values takes place. As defined in the ANSI standard (and probably in BOL as well, though I didn't care to bother), the shorter of the strings is padded to match the length of the longer, after which a byte by byte comparison takes place. So you are comparing 'Hell       ' (yes, there are seven spaces there - unless some HTML god mangles them) to 'Hello world'. There'll be a difference at position 5, and the values are thus unequal.

Hi alok here ,i hope it is not applicable for ado.net

  is the best of comming sequence 2005