SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATALENGTH


DATALENGTH

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

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

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
tommyh
tommyh
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 2000
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
Hugo Kornelis
Hugo Kornelis
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: 18745 Visits: 12426
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
tommyh
tommyh
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2532 Visits: 2000
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
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4798 Visits: 2907
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145619 Visits: 19425
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
My Blog: www.voiceofthedba.com
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4798 Visits: 2907
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66259 Visits: 18570
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

tilew-948340
tilew-948340
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

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