"SQL" Beware of string concatenation leading to length-exploding tables

Please look at the code:

 @ Max  varchar  (max  )  set   @max  =    aaa ...    --          Here are 8,000 a  +    bb    --  join a varchar constant or variable  select  len  ( @max )  

Don't think of course it will return 8002, but 8000,select @max will only get 8,000 a, after two B is gone. We know that the varchar (max) type is not limited by the number of characters, but why?

This is not related to the data type of the @max, but rather to the type of data that is obtained after the concatenation of the string, or to the data type inference of the string constant. In SQL 2005 and SQL (R2), knocking a ' a ', the system will use it as varchar (1), ' AA ' is varchar (2), N ' a ' is nvarchar (1), and ' a ... ' (more than 8,000 a), 05 when it is text,08 then when it is varchar (max), the data type of the constant or variable can be obtained through the system function Sql_variant_property, using the example:

SELECT Sql_variant_property ('a','BaseType')

For more information about this function, see SSMs Help or http://technet.microsoft.com/zh-cn/library/ms178550 (v=sql.105). aspx

So the problem is, since the constant system of more than 8,000 characters is automatically recognized as the big data type, does not appear truncated, why the splicing of the dish, this is because varchar (n) +varchar (n) or =varchar (n), splicing when the system will automatically expand the length of the data, However, the data type is not changed (varchar (n) and varchar (max) should be treated as different data types), and because the N maximum value in varchar (n) is 8000, the maximum varchar (x) +varchar (y) will only get varchar ( 8000), when x+y>8000, the truncation occurs.

Back to the beginning of the example, it is clear that ' aaa ... ' and ' BB ' are varchar (n), after stitching to get varchar (8000), that is, truncated 8,000 ' aaa ... ', so even assigning it to varchar (max) is useless. If ' aaa ... ' again multiple A, the situation is different, this is the text or varchar (max) +varchar (n), for 05, will be reported text and varchar can not splice, for 08, will correctly get no truncation of ' aaa...bb ', Because varchar (max) +varchar (n) =varchar (max).

Ok here, I hope the ape friends in the concatenation of SQL string attention to this problem.

"SQL" Beware of string concatenation leading to length-exploding tables