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

first let me difference between both

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

And if you use fix size suppose 5000 then you can store upto 5000 only if lenght of text increase then you will get error message. so [PageContent] [varchar](max) NOT NULL, is better but if you are sure string lenght will not increase more then 5000 then [PageContent] varchar NOT NULL is better

nchar [ ( n ) ]Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..

nvarchar [ ( n | max ) ] Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

According to me use nvarchar [ ( n | max ) ] so you will not depend on string length

ref: for more information https://msdn.microsoft.com/en-IN/library/ms186939.aspx

Answer 3

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.

Answer 4

"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.

Answer 5

The bigger the varchar, the more space your database will require. The best practice is to allocate as less space as possible, so, in your case, the second code is better.

[PageContent] [varchar](5000) NOT NULL,

In this case you cannot store more than 5000 char in the column.