Hi guys,

I'm new to MS-SQL 2005, I will ask you a bunch of questions and I hope I will not be too boring hehe !

I learned that in MS-SQL 2005, you can now use varchar(max), my question is why not always use varchar(max) since it will automatically use only the space needed ? Is there an advantage in specifying a size other than max when you use a varchar for an entry that can vary in length alot ?

Thank you very much,

Konnan

There are a few diferences between VARCHAR(1-8000) and VARCHAR(MAX).

when you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored. By default sql server will try to accomodate the value 'in row' but if it could not, it will store the large values 'out of row'. When values are stored 'out of row' there will be slight processing overhead in reading the information. Here is a good reference: http://msdn2.microsoft.com/en-us/library/ms189087.aspx

I guess you cannot index a VARCHAR(MAX)/NVARCHAR(MAX) column.

coming back to your question:

I dont think it is bad to use VARCHAR(MAX) is bad. If you are storing smaller piecs of data in a VARCHAR(MAX) column, it will be treated as normal. If you dont want to index the column, then you can definitely go with VARCHAR(MAX) option.

But most people do not advise that. First of all, by having a VARCHAR(MAX) will confuse some one who looks at the data later on. For example, if you want to store a comment of 100 characters or address of 80 characters, why should you go for VARCHAR(MAX)? If you use Address VARCHAR(MAX), comments VARCHAR(MAX), Name VARCHAR(MAX), some one trying to read or write data on a later date will be confused. They will not know what is the expected size of the data and they will be compelled to use LARGE VALUE types always.

Again, these are not rules but conventions.

The storage is only part of the story here. There is also the internals story of the way MAX fields are handled. Since they span pages, the internal code must stream. While it might be nice to have it treat single page versus multiple page differently (single pointer on a single page; multiple on multiple), there is a perf hit for making the decision "does it span", so the engine streams on all MAX fields. The perf hit is small, so it is not a huge deal in all but the largest applications, but if you know your data can fit in a varchar(n) field it is better to use a large n value than use max. Max is a safety net, but if you don't need it, don't put it up.

Peace and Grace,
Gregory A. Beamer

I'm revisiting my old threads, and this is an awesome answer :-)

Thanks Mr. Sebastian !

Konnan

Thanks to you too, Mr. Beamer !

Konnan