This is a good basic question, but the part of explanation is totally wrong. I am referring to this:
However, the test above shows that this is applicable only to CHAR and NOT to VARCHAR data-type (the 3rd part - DATALENGTH(CAST('abc' AS VARCHAR)) - returns 3, while the 4th part - DATALENGTH(CAST('abc' AS CHAR)) - returns 30).
which does not make any sense. When omitting the size of either char or varchar, the default size is 30 for both
. Aside from the fact that omitting the size of character based variable is ill advised, here is what happens in reality:
The first variable named @TestVariable2 has size 50. The set statement:
SET @TestVariable2 = CONVERT(VARCHAR,'abc')
in reality means that convert returns a value 'abc' as varchar(30) (size is omittted, default kicks in), but because the actual @TestVariable2 has a size 50, its value 'abc' is still varchar(50). Datalength function for this value returns 3 of course, because there are no trailing spaces in @TestVariable2, its value is exactly 'abc', its size 50 means that it is capable of taking up to 50 characters.
The second variable named @NextTestVariable2 has size 50, but it is defined as char. The set statement
SET @NextTestVariable2 = CONVERT(CHAR,'abc')
in reality means that convert returns a value 'abc' followed by 27 spaces (size is omittted, default of 30 kicks in), but because NextTestVariable2 has size 50, its value is 'abc' followed by 47 spaces. 20 extra spaces were added to this variable value after convert already returned (30 characters total) and the value is assigned. Datalength function for this value returns 50 because unlike len function, datalength does not ignore trailing spaces.
DATALENGTH(CAST('abc' AS VARCHAR))
returns 3 because size is not specified, 30 is used as default, and the datalength of value 'abc' converted to varchar(30) is still 3. This is not because default size 30 does not apply to varchar, it does, but the datalength of the value is still 3, as it does not have any trailing spaces.
DATALENGTH(CAST('abc' AS CHAR))
returns 30 because size is not specified, 30 is used as default, and the datalength of value 'abc' converted to char(30) is 30 because the value is equal to 'abc' followed by 27 spaces, which datalength does not ignore.