Are there disadvantages to using VARCHAR(MAX) in a table?

sql-server varchar nvarchar
Meiscooldude picture Meiscooldude · Apr 3, 2010 · Viewed 19.4k times · Source

Here is my predicament.

Basically, I need a column in a table to hold up an unknown length of characters. But I was curious if in Sql Server performance problems could arise using a VARCHAR(MAX) or NVARCHAR(MAX) in a column, such as: 'This time' I only need to store 3 characters and most of the time I only need to store 10 characters. But there is a small chances that It could be up to a couple thousand characters in that column, or even possibly a million, It is unpredictable. But, I can guarantee that it will not go over the 2GB limit.

I was just curious if there are any performance issues, or possibly better ways of solving this problem where available.

Answer

John Sansom picture John Sansom · Apr 3, 2010

Sounds to me like you plan to use the varchar(MAX) data type for its intended purpose.

When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types.

For further reading, check out Books Online: char and varchar

Related questions

What is the difference between varchar and nvarchar?

Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?

Read More
What are the main performance differences between varchar and nvarchar SQL Server data types?

I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of …

Read More
What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

What is meant by nvarchar? What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

Read More