Return to Answer

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Edit:

One could always raise a Connect issue for MS. At least they may explain this behaviour.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings

Edit 2, after Martins comment

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX) SELECT @sql = 'B', @nsql = 'B'; select LEN(@sql), LEN(@nsql), DATALENGTH(@sql), DATALENGTH(@nsql) declare @t table(c varchar(8000)) insert into @t values (replicate('A', 7500)) select LEN(c) from @t select LEN(@sql + c), LEN(@nsql + c), DATALENGTH(@sql + c), DATALENGTH(@nsql + c) from @t 

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Edit:

There is an open Microsoft Connect item regarding this issue.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings (and matching Connect item).

Edit 2, after Martins comment:

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX); SELECT @sql = 'B', @nsql = 'B'; SELECT  LEN(@sql),  LEN(@nsql),  DATALENGTH(@sql),  DATALENGTH(@nsql) ;  DECLARE @t table(c varchar(8000)); INSERT INTO @t values (replicate('A', 7500)); SELECT LEN(c) from @t; SELECT  LEN(@sql + c),  LEN(@nsql + c),  DATALENGTH(@sql + c),  DATALENGTH(@nsql + c)  FROM @t; 

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Edit:

One could always raise a Connect issue for MS. At least they may explain this behaviour.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings

Edit 2, after Martins comment

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX) SELECT @sql = 'B', @nsql = 'B'; select LEN(@sql), LEN(@nsql), DATALENGTH(@sql), DATALENGTH(@nsql) declare @t table(c varchar(8000)) insert into @t values (replicate(CAST('A' AS varchar(MAX)), 8000)) select LEN(c) from @t select LEN(@sql + c), LEN(@nsql + c), DATALENGTH(@sql + c), DATALENGTH(@nsql + c) from @t 

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Edit:

One could always raise a Connect issue for MS. At least they may explain this behaviour.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings

Edit 2, after Martins comment

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX) SELECT @sql = 'B', @nsql = 'B'; select LEN(@sql), LEN(@nsql), DATALENGTH(@sql), DATALENGTH(@nsql)   declare @t table(c varchar(8000)) insert into @t values (replicate('A', 7500))   select LEN(c) from @t select LEN(@sql + c), LEN(@nsql + c), DATALENGTH(@sql + c), DATALENGTH(@nsql + c) from @t 

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Edit:

One could always raise a Connect issue for MS. At least they may explain this behaviour.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Edit:

One could always raise a Connect issue for MS. At least they may explain this behaviour.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings

Edit 2, after Martins comment

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX) SELECT @sql = 'B', @nsql = 'B'; select LEN(@sql), LEN(@nsql), DATALENGTH(@sql), DATALENGTH(@nsql) declare @t table(c varchar(8000)) insert into @t values (replicate(CAST('A' AS varchar(MAX)), 8000)) select LEN(c) from @t select LEN(@sql + c), LEN(@nsql + c), DATALENGTH(@sql + c), DATALENGTH(@nsql + c) from @t