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).

SET @TestVariable2 = CONVERT(VARCHAR,'abc') 

SET @NextTestVariable2 = CONVERT(CHAR,'abc')

DATALENGTH(CAST('abc' AS VARCHAR)) 

DATALENGTH(CAST('abc' AS CHAR))

Oleg Netchaev (10/2/2010)

---

This is a good basic question, but the part of explanation is totally wrong.

The storage size is the actual length of data entered + 2 bytes

Oleg Netchaev (10/2/2010)

---

This is a good basic question, but the part of explanation is totally wrong. I am referring to this:

da-zero (10/4/2010)

---

Good question and thanks to Oleg for the more detailed explanation.

@Nakul: no excuses about having no time to do the detailed reasoning Post your question a few days later then, after you've done the research.

A small question for the experts: why isn't SELECT DATALENGTH(@TestVariable2) equal to 5? BOL says for the varchar datatype:



The 2 extra bytes are used for storing the effective lenght of the string. Why doesn't datalength show these bytes?

da-zero (10/4/2010)

---

Good question and thanks to Oleg for the more detailed explanation.

@Nakul: no excuses about having no time to do the detailed reasoning Post your question a few days later then, after you've done the research.

A small question for the experts: why isn't SELECT DATALENGTH(@TestVariable2) equal to 5? BOL says for the varchar datatype:



The 2 extra bytes are used for storing the effective lenght of the string. Why doesn't datalength show these bytes?

andy.roberts (10/4/2010)

---

I don't know why there's this difference between declare and cast/convert