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.

Accepted Answer

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 
```

sql varchar(max) vs varchar(fix)

Solution

Problem