VARCHAR VS VARCHAR(MAX)

What are the differences between VARCHAR and VARCHAR(MAX)  [VARCHAR VS VARCHAR(MAX)]

Which data type should use for defining a column in a table [VARCHAR VS VARCHAR(MAX)]?

	VARCHAR	VARCHAR(MAX)
Bytes	It consumes 1 byte per character.	It consumes 1 byte per character.
Data Length	The maximum number of characters for this data type can hold up to 8000 characters by defining as VARCHAR(8000).	The maximum number of characters for this data type can hold unlimited by defining as NVARCHAR(MAX) MAX indicates that the maximum storage size is (2147483647) 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.
Data Storage Type	Used to store variable length value as String.	Used to store variable length value as String.
Index	Index can be created on VARCHAR(N) data type if needed.	Index can’t be created on VARCHAR(MAX)  data type even though it is needed. It throws the exception error when we create an index on this data type.
Storage	SQL server will use the normal data pages to store the data i.e. it stores the value in a row. Content is dynamic (Variable Length Size Variable) and null, manage storage space efficiently with VARCHAR.	SQL server will use the normal data pages to store the value in a row but if it could not then it will store the value out of row. i.e. It uses the normal data pages until the content actually fills 8k of data. When overflow happens on it, data is stored as old TEXT Data type and a pointer is replacing the old content. Does not allow UNICODE characters and content is variable size for storing.
	Query for Checking whether the data is stored in “in a row” OR “out of row”. SELECT alloc_unit_type_desc,  page_count FROM sys.dm_db_index_physical_stats    (DB_ID('TestDB'),     OBJECT_ID('dbo.TestVarMax'),     NULL, NULL , 'DETAILED')
Use When?	Use VARCHAR data type when content (data) size is variable, does not have any UNICODE characters and / or NULL and content length is lesser than or equal to 8000 characters. VARCHAR  exactly occupies 1 byte.	Use VARCHAR(MAX) data type when content (data) size is variable, has any UNICODE characters and / or  NULL and content length is greater than or exceeds  8000 characters. Note: Unicode Example: ¥ ¼ Ñ ñ (Japanese, Korean, etc.) NVARCHAR  exactly occupies 2 bytes.
Optional Parameter value	The optional parameter value N [VARCHAR(N)] is not specified in the variable declaration or column definition then it is considered as 1. DECLARE @VarChar varchar = 'VarChar' PRINT DATALENGTH(@VarChar) OUTPUT: 1	The optional parameter value MAX [VARCHAR(MAX)] is not specified in the variable declaration or column definition then it is considered as 1. DECLARE @VarChar varchar = 'VarChar' PRINT DATALENGTH(@VarChar) OUTPUT: 1
Data Conversion	DECLARE @Name VARCHAR(50) = 'Govindaraj Kanniappan' SELECT CAST(@Name AS VARCHAR) Name, DATALENGTH(CAST(@Name AS VARCHAR)) NameLength OUTPUT : Name NameLength Govindaraj Kanniappan 21	DECLARE @Name VARCHAR(MAX) = 'Govindaraj Kanniappan' SELECT CAST(@Name AS VARCHAR(MAX)) Name, DATALENGTH(CAST(@Name AS VARCHAR(MAX))) NameLength OUTPUT : Name NameLength Govindaraj Kanniappan 21
Example	DECLARE @Name VARCHAR(8000) = '' DECLARE @Loop INT = 1 WHILE @loop <= 19000 BEGIN        SET @Name += 'A'        SET @Loop += 1 END PRINT DATALENGTH(@Name) OUTPUT: 8000	DECLARE @Name NVARCHAR(MAX) = '' DECLARE @Loop INT = 1 WHILE @loop <= 19000 BEGIN        SET @Name += 'A'        SET @Loop += 1 END PRINT DATALENGTH(@Name) OUTPUT: 38000
Performance	VARCHAR(N) gives better performance result compared to VARCHAR(MAX).  Please try with the following examples and get the results for changing the data type from VARCHAR(N)  to VARCHAR(MAX).  DECLARE @FirstName VARCHAR(50),               @Loop INT = 0,         @StartTime DATETIME = GETDATE() WHILE(@Loop < 1000000) BEGIN    SELECT @FirstName = 'Govindaraj Kanniappan', @Loop += 1 END SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms' GO 10 --  GO Statement can also be used to excute batch of T-Sql statement multiple times

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF