sql server - 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