SQL: VARCHAR(N) vs VARCHAR(MAX)

  SQL:  VARCHAR(N) vs VARCHAR(MAX)

Reference

When choosing data type, we should consider the database design, performance, compression, and indexes in mind.

We can define a specific range for the varchar (n) data type, and it is the recommended way to do so.

Varchar (n) can store up to 8000 bytes of data using varchar (8000) data type.

Varchar (max) can store data up to 2 GB.

varchar(max)	varchar(n)
We can store up to 2 GB of data in this data type	We can store up to 8000 bytes data in this data type
It uses the allocation unit IN_ROW_Data up to 8000 bytes of data. If data is more than 8000 bytes, it uses the LOB_Data page and stores its pointer in the IN_ROW_Data page	It stores data in the standard data page
We cannot create an index on the key column of the varchar(max) data type	We can create an index on this data type
We cannot compress the LOB data	We can compress data for this data type
Data retrieval and updation on the LOB data is relatively slow	We do not face such issue in the varchar(n) data type

Performance Comparison:

Let’s insert 10,000 records into each of the tables we created earlier. We want to check the data insertion time. You can use the ApexSQL Generate tool to insert the data without writing the t-SQL code for it.

In the following screenshot, you can note the following.

• Employee_varchar_2000 insertion time 0.08 Seconds
• Employee_varchar_4500 insertion time 0.19 Seconds
• Employee_varchar_8000 insertion time 0.31 Seconds
• Employee_varchar_Max insertion time 2.72 Seconds