Yesterday, a colleague was attempting to compare a text column in a table against a literal string and kept coming up against the old
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator. error.

He was only trying to do some updates based on known data, so I suggested he converted the text column to VARCHAR before doing the comparison.

We used CONVERT(VARCHAR, <Text Column>) = '<String to compare>') and were surprised that it didn't match. We forced the CONVERT to use VARCHAR(100) and this seemed to work ok, so this got me thinking about what a CONVERT to VARCHAR without a length defaulted to.

SELECT CONVERT(VARCHAR,'123456789012345678901234567890123456789012345678901234567890123456789')
resulted in
123456789012345678901234567890

SELECT DATALENGTH(CONVERT(VARCHAR,'123456789012345678901234567890123456789012345678901234567890123456789'))
resulted in
30

So, CONVERT(VARCHAR, ) converts the contents to a VARCHAR(30) (at least in SQL Server 2005 it does)