Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DATALENGTH Expand / Collapse
Author
Message
Posted Saturday, October 02, 2010 2:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,382, Visits: 1,775
Comments posted to this topic are about the item DATALENGTH

Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #997247
Posted Saturday, October 02, 2010 3:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 1,676, Visits: 1,745
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



Post #997249
Posted Sunday, October 03, 2010 3:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 09, 2011 2:53 PM
Points: 35, Visits: 8
Thank you for the explanation!!
Post #997277
Posted Sunday, October 03, 2010 10:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,382, Visits: 1,775
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #997350
Posted Monday, October 04, 2010 12:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 12,208, Visits: 9,175
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #997369
Posted Monday, October 04, 2010 2:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:33 PM
Points: 2,526, Visits: 530
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
Post #997391
Posted Monday, October 04, 2010 3:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #997404
Posted Monday, October 04, 2010 3:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 17, 2014 3:43 AM
Points: 146, Visits: 122
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
Post #997407
Posted Monday, October 04, 2010 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 5,797, Visits: 8,015
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
Post #997408
Posted Monday, October 04, 2010 3:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 5,797, Visits: 8,015
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
Post #997414
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse