NVARCHAR(MAX) vs NVARCHAR(n)

Question

NVARCHAR(MAX) vs NVARCHAR(n)

Megistal

SSCrazy Eights

Points: 8787
More actions
July 21, 2008 at 1:34 pm

#381810

Hi
I would like to know more about how and why does it take 4 bytes to store an NVARCHAR(MAX) character.
I know that it's maximum size is 2 gigs (2^31) and it can store up to 536,870,912 characters.
However an NVARCHAR(n) is limited to 8000 bytes and can store 4000 characters.
The ratio for NVARCHAR(n) is 2 bytes per character which is normal for UNICODE data.
Then how NVARCHAR(MAX) store it's data? Because it seems that it's data is store on 4 bytes instead of two.
Same difference ratio apply for VARCHAR(MAX) (2 bytes per character, maximum of 1,073,741,824 characters) and VARCHAR(n) (1 byte per character, maximum of 8000 characters) which use ANSI instead.
Could someone please explain why?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not sure of the exact cause, but it's probably because it's being stored outside of the usual row structure of the tables.

But I have to admit that I'm kind of guessing, because I've never run into this limitation. Didn't even know about it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Megistal:

These are not the numbers that I get. Can you please supply a script that demonstrates what you are saying?

Thanks,

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

I think the following link can answer your question...

http://www.sqlserverandxml.com/2008/01/varcharnvarchar-n-vs-max.html">

http://www.sqlserverandxml.com/2008/01/varcharnvarchar-n-vs-max.html

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Megistal SSCrazy Eights Points: 8787 More actions · Answer 4

Hi GSquared

I didn't run into that issue... well not yet. I must size some tables with NVARCHAR(MAX) right now and I had that kind of question in my mind for some times.

Megistal SSCrazy Eights Points: 8787 More actions · Answer 5

Hi rbarryyoung

I'm also confuse about these numbers.

I can provide you with a reference where I got those numbers that I assume are right (but there could be a mistake also)

The book is: MCTIP Self-Paced Training Kit 70-443

Designing a database server infrastructure using microsoft sql server 2005

J.C. Mackin & Mike Hotek

On page 452

Megistal SSCrazy Eights Points: 8787 More actions · Answer 6

Hi Atif Sheikh

I've read some links that were posted inside the link you posted. It explains some interesting informations but I do not seem to find how much (and explanation) bytes are needed for each character in a NVARCHAR(MAX) data type (which seem up to now to be 4 bytes per character)

I do understand that MAX data type fall outside the 8060 bytes available in a page and are considered "off-page" using pointers but I wonder why it's taking so much space.

Thank you for your link.

Megistal SSCrazy Eights Points: 8787 More actions · Answer 7

I've been reading book 70-444 and it contradict 70-443 for Unicode data types.

IMO I believe 70-444 has the right answer. When using Unicode, it take 2 bytes for each character.

I've included a screenshot of 70-444.

The easy way to compare from both version is to look at the NTEXT from both screenshot.