MS SQL : nvarchar(max) vs nvarchar(SIZE)

Posted By: turrican | Nov 23rd, 2008 @ 1:58 AM
page 1 of 1
Comments: 4 | Views: 2733
#Nov 23rd, 2008 @ 1:58 AM
turrican
turrican
Condemnation without investigation is the height of ignorance! - Albert Einstein

An earlier discussion made me ponder this :

MS SQL, if the size of the field is known, would you recommend using nvarchar(max) or nvarchar(SIZE)?

#Nov 23rd, 2008 @ 2:13 AM
TommyCarlier
TommyCarlier
I want my scalps!

If the maximum size is known and you're 100% certain it will NEVER change, I think NVARCHAR(size) is good. It makes it very explicit. If you're not sure what you should pick as a maximum size (like your example of the forum signature), I think NVARCHAR(MAX) is better.

Disclaimer: the opinions posted in this message are my personal opinion and should not be taken as expert advice. Wink

#Nov 23rd, 2008 @ 2:15 AM
turrican
turrican
Condemnation without investigation is the height of ignorance! - Albert Einstein
*lol* ok... I'll try to ask this in a pure SQL forum I guess. Would be very nice if MSSQL knew this or altered it automtically, like when it gets above a certain size it'll know "ok, this is a big text chunk, let us optimize the field for it."
#Nov 23rd, 2008 @ 7:01 AM
littleguru
littleguru
<3 Seattle
The only difference is the length. AFAIK (n)varchar are stored external of the table and the space for it is allocated as required. Now if you want to limit the size use size < MAX otherwise use MAX. Btw. MAX is also limiting to a concrete value.
#Nov 24th, 2008 @ 7:44 AM
figuerres
figuerres
???

VARCHAR in sql is like string in .net
N means National Text / Unicode

CHAR or NCHAR are an "Array of"

if you have the text "ABC"  in you have a varchar(8)  you get back "ABC"
if you use char(8) you get back "ABC______"  ie filled with spaces to 8 chars

when sql creates storage for varchar it has to use a method that manages blocks of text and pointers from the data row to the block.
it also has to do lenght plus text in building an index on varchar.
fixed text (char)  always fits a given size and is simpler for sql to manage.

 

So yes turrican sql does know, and does what it can with it... but a string is a string.

page 1 of 1
Comments: 4 | Views: 2733
Microsoft Communities