Very true. A few of my troubleshooting assignments ended up finding this as the culprit.

You may also need to read this Column Length and data length

one more senario is when using ISNULL...

ISNULL(arg1,arg2) -> return values datatype is same as datatype of arg1

so if I write

DECLARE @arg1 Varchar(2)=NULL

DECLARE @arg1 Varchar(10)='satyajit'

SELECT ISNULL(@arg1,@arg2)

O/p will be 'sa' ..... So for me use of COALESCE is a better approach always as its return values datatype is set in accordance with highest datatype precedence

for more detail ...

http://beyondrelational.com/modules/2/blogs/381/posts/15549/the-null-dilemma-the-null-is-not-equal-to-null-part-2.aspx

This is in-fact triggering a very interesting discussion. Just wanted to add another silent killer that may have happened to many of us.

DECLARE @a VARCHAR(MAX)

SELECT @a = REPLICATE('a', 10000)
SELECT LEN(@a) -- returns 8000

SELECT @a = REPLICATE('a', 5000) + REPLICATE('a', 5000)
SELECT LEN(@a) -- returns 8000

Note that the string got truncated at 8000 though we would expect to get 10000 characters in the variable. Most common scenarios where this happened to me is when trying to build a huge dynamic query or building an email body from TSQL. To ensure that the strings are not truncated incorrectly, we need to make sure that at least one value involved in the string operation is of MAX data type so that the result is also a MAX value.

For example:

DECLARE @a VARCHAR(MAX) = ''
SELECT @a = REPLICATE(CAST('a' AS VARCHAR(MAX)), 10000)
SELECT LEN(@a) -- returns 10,000

Jacob, Here is the one about it http://beyondrelational.com/blogs/madhivanan/archive/2008/07/15/varchar-max-datatype-and-replicate-function.aspx

1 more post is here :)

http://beyondrelational.com/modules/2/blogs/381/posts/7649/nvarcharmaxvarcharmax-in-sql-server.aspx?pa=liked#pat

To ensure that the strings are not truncated incorrectly, we need to make sure that at least one value involved in the string operation is of MAX data type so that the result is also a MAX value.

It comes down to the return type of the function. The MSDN documentation specifies the return type as:

Returns the same type as string_expression.

Remember that varchar(max) and varbinary(max) are BLOBs so are different types to varchar(n) and varbinary(n). You wouldn't consider char(n) the same type as varchar(n) would you? :)

Moreover, this functionality isn't particular to the REPLICATE function. Consider the following:

DECLARE @a varchar(5000);
SELECT @a = REPLICATE('a', 5000);
SELECT LEN(@a), LEN(@a + @a);

Would you be surprised to learn that LEN(@a + @a) is 8,000? And it's not the LEN function which is truncating the input! @a + @a returns a string of just 8,000 characters, not 10,000. If you don't believe it, try creating a new table and allow sql to assign the appropriate type for @a+@a:

DECLARE @a varchar(5000) = REPLICATE('a', 5000);
SELECT @a + @a AS testType INTO testblob;
-- check the type of the field created in object explorer or with the following:
SELECT LEN(testType), DATALENGTH(testType) FROM testblob;
DROP TABLE testblob;

If that still surprises you, how about:

DECLARE @i int = 2147483647; -- maximum value of signed int
SELECT @i+1;

which yields:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

I hope this has hammered the point home. If it's left you more confused, it's probably a good thing as it's pointed out that you need to review your sql types :D

Thanks for all those comments guys :) Btw forgive my ignorance is there a way to post images (from our box) within our blog post? I saw only an option which was asking for an image URL.

@Vadivel,

Currently uploading images are a two step process. If you are using windows live writer to publish your blog posts, then it is quite simple. But to upload an image directly when writing a post on the web site or when writing a comment, you will need a two step process to upload and use an image.

If you have a blog (in your case you have), you can see a link 'images' on the top of the page which allows you to upload an image. After the image is uploaded, you can take the URL and use it in a post/comment etc.

We are about to release a separate section at BR which allows a user to quickly upload an share an image/screen-shot etc. This will make the whole process much easier. In case you are interested to have an early look into it, let me know and I will send you the details.

As I always used length with varchar so not came to issue like this, but good to know this.