SQL-character data (varchar)

I just considered whether to write every day! However, it is time to continue. Otherwise, you will not be able to live well. There are old and small ones. Besides, writeArticleLong memory! Improve yourself! :)

Okay, no nonsense! I checked the email and received the newsletter from sqlservercentral.com. There is a problem in this issue:

 Declare @ Str Varchar ( Max ) Select @ STR = replicate ( '#' , 10000) + replicate ( Cast ( '#'   As   Varchar ( Max ), 8000) + '#' +'#' + '#'   Select Len (@ Str) Output   Of The above code In   SQL Servers 2005 Is : A. 16003b. 18005c. 8000d. Error: incorrect syntax near '.

The data type varchar may not be used much, but it is even more unclear about varchar (max. As a result, I chose C, Sorry-you were wrong.

In my understanding, the length of varchar in SQL Server 2005 is only 8000 bytes, and the maximum length is 8000 bytes. So I chose C, a wrong answer.

It turns out that varchar can accommodate up to 2,147,483,648 bytes in varchar (max), which is the case in SQL Server 2005. In Oracle 9i, varchar has only 4000 bytes. In MySQL, there are 65,535 bytes.

So will the answer be B? No, B is also wrong. It won't be D, or at least those SQL statements won't go wrong.

The correct answer is.

The reason is: although the replicate function can replicate specified characters (strings) for a certain number of times according to your ideas, it is not infinite) if the data type of is varchar (max) or nvarchar (max), replicate will get only one 8000 bytes value, and will be automatically truncated when the data type is exceeded.

In this way, the first replicate ('#', 10000) can only get 8000 '#', while the second one explicitly converts '#' to varchar (max ), however, because 8000 is specified, there are only 8000 '#'.

With the last three '#' added, the result of the final Len function is 16003.

 

Although the purpose of this problem is the replicate function, varchar (max) is also in it ).