Skip to main content
S
SpiderFromMars
Inspiring
December 11, 2009
Answered

VARCHAR(MAX) Sql Server 2008

  • December 11, 2009
  • 1 reply
  • 1779 views

What is the maximum bite number you should use in VARCHAR before resorting to VARCHAR(MAX)? For instance, is it efficient to use VARCHAR(900) or VARCHAR(2500)? At what point is it most efficient to use the MAX?

This topic has been closed for replies.
Correct answer Dan_Bracuk

Microsoft says:

If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar, we recommend the following:

  • Use char when the sizes of the column data entries are consistent.
  • 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.

1 reply

D
Dan_BracukCorrect answer
Inspiring
December 12, 2009

Microsoft says:

If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar, we recommend the following:

  • Use char when the sizes of the column data entries are consistent.
  • 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.
S
SpiderFromMarsAuthor
Inspiring
December 17, 2009

Thank you for your response in regards to this. It helps alot when it comes to optimizing my database.

One other question that maybe you can help me with: What are the optimum VARCHAR values to use?

In other words, should the value be in blocks of 32 to be most efficient?   VARCHAR(32)  instead of VARCHAR(25), VARCHAR(64) instead of VARCHAR(50), etc....

S
SpiderFromMarsAuthor
Inspiring
December 22, 2009

The short answer is, "as small as you can make it while still meeting the requirements of the field".  The long answer depends on a variety of other things.


After some experimentation, I discovered that SQL Server 2008 does work in blocks of 32-bit data.

For instance, if you try to insert 195 characters of data into a VARCHAR(200), you will get a truncation error.

This is because you are trying to put 6240 bits of data (195 X 32) into 6144 bits of space ( [abs(200/32)] * 32 ).  So, to get this to be inserted into the database, the field should be VARCHAR(224) (7 X 32).

Therefore, the short answer to the long answer is always use a factor of 32 when setting up VARCHAR values for your database.

Thanks so much for your help with this!

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices