Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

difference between varchar(500) vs varchar(max) in sql server

Tags:

I want to know what are pros and cons while using varchar(500) vs varchar(max) in terms of performance, memory and anything else to consider?

  • Will both use same amount of storage space?

Is the answer differ in case of sql server 2000/2005/2008?

like image 560
Prashant Lakhlani Avatar asked Sep 10 '10 07:09

Prashant Lakhlani


People also ask

What is the difference between varchar and varchar Max?

VARCHAR(MAX) is different from VARCHAR because it supports character strings up to 2 GB (2,147,483,647 bytes) in length. You should consider using VARCHAR(MAX) only when each string stored in this data type varies considerably in length, and a value might exceed 8000 bytes in size.

When should I use varchar Max in SQL Server?

If you use char or varchar, we recommend to: 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 string length might exceed 8,000 bytes.

What is Max length of varchar in SQL Server?

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section 8.4.

Is varchar Max same as varchar 8000?

About varchar(MAX)If your data is longer than 8000 characters varchar(MAX) is what you need. You can store up to 2GB size of data this way. In varchar(MAX) fields if your data size is shorter than 8000 characters your data is stored in row automatically (therefore the data execution is faster).


2 Answers

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.

To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. 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. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. In fact, BLOBs are being deprecated in future releases of SQL Server.

Credit: http://www.teratrax.com/articles/varchar_max.html


In SQL Server 2005 and SQL Server 2008, The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

Full Interesting read for you: http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

Reference: http://msdn.microsoft.com/en-us/library/ms143432.aspx

like image 68
shamittomar Avatar answered Sep 19 '22 02:09

shamittomar


A VARCHAR(MAX) column will accept a value of 501 characters or more whereas a VARCHAR(500) column will not. So if you have a business rule that restricts a value to 500 characters, VARCHAR(500) will be more appropriate.

like image 41
onedaywhen Avatar answered Sep 18 '22 02:09

onedaywhen