October 2, 2010 at 2:06 pm
Comments posted to this topic are about the item DATALENGTH
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
October 2, 2010 at 3:08 pm
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.
Finally,
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.
Oleg
October 3, 2010 at 3:24 am
Thank you for the explanation!!
October 3, 2010 at 10:29 pm
Oleg Netchaev (10/2/2010)
This is a good basic question, but the part of explanation is totally wrong.
That's why said "the test above shows that..." - I didn't have the time to do the detailed reasoning when I encountered this issue (during a failed upgrade!). Your explanation is perfect, and I thank-you for the same.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
October 4, 2010 at 12:22 am
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?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 4, 2010 at 2:08 am
I agree, the result is correct but the explanation is wrong. And this is a good example why you should never omit any default statements even it may be tempting for lazy programmers. 😀
Oleg Netchaev (10/2/2010)
This is a good basic question, but the part of explanation is totally wrong. I am referring to this:
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
October 4, 2010 at 3:07 am
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.
October 4, 2010 at 3:17 am
Slight quibble with Oleg's reply.
"When omitting the size of either char or varchar, the default size is 30 for both" implies that the default size for varchar or char in a declare, cast or convert would be 30. This is correct for char and varchar but for declare the default size is 1. If the problem had been specified with no lengths in the declare statements then the solution would have been 1, 1, 3, 30.
I don't know why there's this difference between declare and cast/convert
Andy
--------------------------------------------------------------
“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
October 4, 2010 at 3:19 am
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.
Agreed, on both parts. The explanation is simply wrong; people who come to this site for the question but don't read the discussion will walk away with incorrect information. If you don't know why something happens, find it out first. Or don't, but in that case you should not post a question with your own made-up explanation either.
Nakul, I am sorry if I come across too harsh. I admire everyone who has the courage to step forward and submit questions for this website. But I also think that everyone who does that should be utterly aware of the responsibility that comes with it. The number of people reading the follow-up discussion is far less than the number of people who only answer the question and read the explanation. Since the goal of this site is (in my opinion) to educate, the submitter of the question has a huge responsibility to make sure the explanation is correct.
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?
Because DATALENGTH returns the length of the actual data, not the actual storage space used on disk (which is actual data + effective length for varying length column + null bitmap + some other overhead)
October 4, 2010 at 3:23 am
andy.roberts (10/4/2010)
I don't know why there's this difference between declare and cast/convert
Nobody does, I think. 🙂
Probably for historical (hysterical?) reasons.
But this difference has already been the subject of many QotD's, and this is also exactly why Håkan suggests (and I fully agree with him) that one should never rely on the default, but always explicitly declare the length.
October 4, 2010 at 3:24 am
Hugo Kornelis (10/4/2010)
Nakul, I am sorry if I come across too harsh.
Not at all, Hugo. In fact, I like it that we have a frank and honest community which draws attention to the important details. I really appreciate the feedback, and am not at all offended.
No apologies necessary - I am the one who needs to apologize, not you or anyone else who comes to the site.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
October 4, 2010 at 5:19 am
Hugo Kornelis (10/4/2010)
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.
Agreed, on both parts. The explanation is simply wrong; people who come to this site for the question but don't read the discussion will walk away with incorrect information. If you don't know why something happens, find it out first. Or don't, but in that case you should not post a question with your own made-up explanation either.
Nakul, I am sorry if I come across too harsh. I admire everyone who has the courage to step forward and submit questions for this website. But I also think that everyone who does that should be utterly aware of the responsibility that comes with it. The number of people reading the follow-up discussion is far less than the number of people who only answer the question and read the explanation. Since the goal of this site is (in my opinion) to educate, the submitter of the question has a huge responsibility to make sure the explanation is correct.
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?
Because DATALENGTH returns the length of the actual data, not the actual storage space used on disk (which is actual data + effective length for varying length column + null bitmap + some other overhead)
Well if one wants to be picky it does return the nr of bytes needed to represent the data. Hence why
select datalength(N'hi'), datalength('hi')
gives 2 different results. 4 vs 2. Because Unicode requires double the space.
/T
October 4, 2010 at 5:48 am
tommyh (10/4/2010)
Well if one wants to be picky it does return the nr of bytes needed to represent the data. Hence why
select datalength(N'hi'), datalength('hi')gives 2 different results. 4 vs 2. Because Unicode requires double the space.
That is what I intended to say, but apparently didn't bring across.
DATALENGTH returns the length (in bytes) of the storage for the actual data. For Unicode, that is 2 bytes per character. The seperate storage of the length is not included, since this is not part of the actual data.
October 4, 2010 at 5:55 am
Hugo Kornelis (10/4/2010)
tommyh (10/4/2010)
Well if one wants to be picky it does return the nr of bytes needed to represent the data. Hence why
select datalength(N'hi'), datalength('hi')gives 2 different results. 4 vs 2. Because Unicode requires double the space.
That is what I intended to say, but apparently didn't bring across.
DATALENGTH returns the length (in bytes) of the storage for the actual data. For Unicode, that is 2 bytes per character. The seperate storage of the length is not included, since this is not part of the actual data.
I almost thought that (probably me missing something in translation that every native english speaker would get right away). But since it left me confused i thought id might try to add a bit of more clarification (or confusion).
/T
October 4, 2010 at 6:56 am
Nakul Vachhrajani, very good question.
Unfortunately
Nakul Vachhrajani
That's why said "the test above shows that..." - I didn't have the time to do the detailed reasoning when I encountered this issue (during a failed upgrade!). Your explanation is perfect, and I thank-you for the same.
to me you lost credability with this comment.
You did a big disservice to the readers of this forum when you posted such a great question with the wrong answer. Your comment after, at least to me was one of indifference.
Oleg provided a very good explanation, thanks Oleg. Hugo expanded upon it as well. But as Hugo stated earlier
Hugo;
The number of people reading the follow-up discussion is far less than the number of people who only answer the question and read the explanation. Since the goal of this site is (in my opinion) to educate, the submitter of the question has a huge responsibility to make sure the explanation is correct.
So true. I used to do the same - check in to see the daily subjects, answer the question then on to other things, not to login again until the next day or so. It was only recently (last few months) that I started actually reading the discussions and even feably attempting at providing a few QOD's as well as taking part in the dialogs which follow.
Again good question though.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply