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 Monday, October 4, 2010 3:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:55 AM
Points: 1,414, Visits: 1,824
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #997415
Posted Monday, October 4, 2010 5:19 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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

Post #997456
Posted Monday, October 4, 2010 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #997473
Posted Monday, October 4, 2010 5:55 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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
Post #997477
Posted Monday, October 4, 2010 6:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #997523
Posted Monday, October 4, 2010 7:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
I have altered the explanation a bit to better show what the reason is for this.

My apologies for this.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #997574
Posted Monday, October 4, 2010 9:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
Steve Jones
My apologies for this


Sorry, in this case, I don't feel that the responsibility falls upon you to do the research for the correct answer. The answer provided sounded viable, which to the average user would be taken as gospel. If you look the top of the homepage states that there are 1.3 million users of this web site. Yet if you look at the numbers of comments, and who they are you repeatedly see the same few. When you look at the numbers of answerers, you see a disproportionate number of posters. So now one must ask - How many of the people who answered this question think that what they read is correct?

This reminds me of an interview I took many years ago where my new boss was going to explain to me how indexes worked. I cringed and bit my lip when his explanation, based upon an article he had recently read in a particular magazine had the definitions for clustered vs nonclustered indexes reversed. For a long time he believed this because he read it.


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
Post #997637
Posted Monday, October 4, 2010 12:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #997778
Posted Monday, October 4, 2010 6:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
I got the wright answer because I knew that a char(50) would return 50, so thanks for the easy choice

My question now is: how someone know that "When omitting the size of either char or varchar, the default size is 30 for both" ? Where can I find this type of information because I looked on the help pages for "data type" and I found nothing about default size...

Oups! ok.. Found it! I should have read the remark...
Post #997998
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse