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 storage size is the actual length of data entered + 2 bytes
The 2 extra bytes are used for storing the effective lenght of the string. Why doesn't datalength show these bytes?
Although it's true that Varchar takes the length of the string plus 2 bytes to actually store the information on the hard drive, it does not have anything to do with the DATALENGTH function.
The difference between of LEN() and DATALENGTH() functions only has to do with trailing spaces -- not the actual number of characters it takes to store them. LEN('123 ') returns 3 and DATALENGTH('123 ') returns 4.