sql varchar(max) vs varchar(fix)

Question

Every time I confused for selecting varchar(max) or varchar(fix) datatype. suppose I have a data column that will be around 5000 varchar. a column is not null type.

should i set it varchar(max) not null or varchar(5000) not null.

same thing in case of a nullable data type.

CREATE TABLE [dbo].[tblCmsPages](
[CmsPagesID] [int] IDENTITY(1,1) NOT NULL,
[PageName] [varchar](250) NOT NULL,
[PageContent] [varchar](max) NOT NULL,
[Sorting] [int] NOT NULL,
[IsActive] [bit] NOT NULL) 

//or

CREATE TABLE [dbo].[tblCmsPages](
[CmsPagesID] [int] IDENTITY(1,1) NOT NULL,
[PageName] [varchar](250) NOT NULL,
[PageContent] [varchar](5000) NOT NULL,
[Sorting] [int] NOT NULL,
[IsActive] [bit] NOT NULL

//[PageContent] will be 5000 char or single char or null then what should i take.

One another think I want to know. What is the main difference between null and not null. Is it only for validation check and what is an effect on performance.

Answer 1

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 

Answer 2

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.