cool hit counter [SQL] Beware of string splicing leading to length explosion_Intefrankly

[SQL] Beware of string splicing leading to length explosion

Look at the code:

SET @max='aaa...' --Here it is.8000 sizea
         +'bb' -- Connect avarchar Constants or variables


Don't assume it will return 8002, but 8000, select @max will only get 8000 a, the last two b gone. We know that the varchar (max) type is not limited by the number of characters, but why is this happening?

This actually has nothing to do with@max independent of the data type of the, Rather, it has to do with the type of data obtained after string splicing, Or rather, Related to data type inference for string constants。 (located) atSQL 2005 harmonySQL 2008(R2) in, Knock one.'a', The system will treat it asvarchar(1),'aa' thenvarchar(2),N'a' thennvarchar(1), but (not)'a...'( surpass8000 sizea) this (Cantonese),05 In will be when it istext,08 Then when it isvarchar(max), The data type of a constant or variable can be determined by the system functionSQL_VARIANT_PROPERTY gain, Example of use:


For more information about this function, see SSMS Help or

So here's the question., Since more than8000 Constants of up to three characters are automatically recognized by the system as big data types, No truncation will occur, Why did the splicing stop when it did?, This is becausevarchar(n)+varchar(n) or=varchar(n), The system automatically expands the data length when splicing, but does not change the data type(varchar(n) together withvarchar(max) should be considered as different data types), also becausevarchar(n) hit the targetn The maximum value taken is8000, consequentlyvarchar(x)+varchar(y) The maximum will only getvarchar(8000), properx+y>8000 time, Then there will be a truncation。

Going back to the example at the beginning of the article, It's clear.,'aaa...' harmony'bb' allvarchar(n), Splicing givesvarchar(8000), That is, the truncated8000 size'aaa...', So even if you assign it tovarchar(max) It won't help.。 in case'aaa...' multiple morea, Things are different again., This is when thetext perhapsvarchar(max)+varchar(n), as far as sth is concerned05, reportabletext together withvarchar Cannot be spliced, as far as sth is concerned08, will correctly get the truncation-free'', owing tovarchar(max)+varchar(n)=varchar(max)。

OK is here, I hope the ape friend in stitching SQL string when paying attention to this problem.

1、The ultimate solution for asynchronous processing in WeChat applets asyncawait
2、GoogleChromeContinues to pull away from rivals with strong performance in browser market
3、google Microsoft corporation Ali proper name American Express Internship Interview
4、Getting Started with ZooKeeper
5、Python KNN algorithm to determine word similarity small case

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送