sql varchar(max) vs varchar(fix)

MSDN

• Use varchar when the sizes of the column data entries vary considerably.
• Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is 8000. If the length is greater than 8000, you have to use the MAX specifier as the length. If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):

The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

UPDATE :- I found a link which I would like to share:-

Here

There is not much performance difference between Varchar[(n)] and Varchar(Max). Varchar[(n)] provides better performance results compared to Varchar(Max). If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then using this Varchar[(n)] data type provides better performance compared to Varchar(Max).Example: When I ran the below script by changing the variable @FirstName type to Varchar(Max) then for 1 million assignments it is consistently taking double time than when we used data type as

Varchar(50) for variable @ FirstName.
DECLARE @FirstName VARCHAR(50), @COUNT INT=0, @StartTime DATETIME = GETDATE()
WHILE(@COUNT < 1000000)
BEGIN
SELECT @FirstName = 'Suraj', @COUNT = @COUNT +1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO 

You have clear idea about data , it would be not exceed than 5000, I prefer to varchar(n)(varchar(5000).

If you want to selection between varchar(n) and varchar(max), please care below point:

1. Where appropriate, use VARCHAR(n) over VARCHAR(MAX)

   a. for reasons of good design if not performance benefits, and

   b. because VARCHAR(MAX) data does not compress

2. Storing large strings takes longer than storing small strings.

3. Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.

4. Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.

5. Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.

"around 5000" - if you don't know, then setting is to max would probably be best bet. On the other hand if you know it will not exceed 6000, you might as well use this ceiling to save some space.

It depends very much on how much data you're storing, how much it is accessed and how important it is. There is no magic rule that suits all I'm afraid.

As to allow null or not null: Personally I prefer to, in most cases, allow nulls. First of all a null means something, and secondly I prefer to set such restrictions in the front-end. But it depends on the data stored of course.