Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATALENGTH


DATALENGTH

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1937 Visits: 2139
Comments posted to this topic are about the item DATALENGTH

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 1811
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
jaypal_raj
jaypal_raj
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 8
Thank you for the explanation!!
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1937 Visits: 2139
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18525 Visits: 13248
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2735 Visits: 604
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. :-D

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
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
cengland0
cengland0
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 Visits: 1300
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.
andy.roberts
andy.roberts
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 124
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8744 Visits: 11717
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)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8744 Visits: 11717
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search