Comment Feed for MS SQL : nvarchar(max) vs nvarchar(SIZE) (TechOff on Channel 9)

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

figuerres — Mon, 24 Nov 2008 15:44:15 GMT

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.

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

Christian Liensberger — Sun, 23 Nov 2008 15:01:39 GMT

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.

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

turrican — Sun, 23 Nov 2008 10:15:39 GMT

*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."

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

Tommy Carlier — Sun, 23 Nov 2008 10:13:02 GMT

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. ;-)