SQL Server Portal

I received an inquiry from one of my blog readers “Mr. Arun” and he wanted to store more than 8,000 characters in a column. Generally the length of  a varchar(Max) data type consider it as a 8000 characters and above. So I suggested him to use VARCHAR(MAX). But even if you use VARCHAR(MAX), you should be careful while working on more than 8000 characters.
Let me explain the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Column_varchar] VARCHAR(MAX)
)
GO

Step 2 :
Insert 10,000 characters in the column ([Column_varchar]). Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1, REPLICATE('A',10000))

Step 3 :
Check the length of column ([Column_varchar]) to see if 10,000 characters are inserted or not. Given below is the script.

USE tempdb
GO
SELECT DATALENGTH([Column_varchar]) AS [Column Length]
FROM tbl_sample
--OUTPUT

Step 4 :
Ooopps…… It only inserted 8000 characters even though I passed 10,000. Basically the solution is that you need to cast the characters as VARCHAR(MAX) before insertion and insert it again.

USE tempdb
GO
INSERT INTO tbl_sample VALUES
(2, REPLICATE(CAST('A' AS VARCHAR(MAX)),10000))

Step 5 :
Check the length of column ([Column_varchar]) AGAIN and see whether 10,000 characters are inserted or not. Given below is the script.

USE tempdb
GO
SELECT DATALENGTH([Column_varchar]) AS [Column Length]
FROM tbl_sample
--OUTPUT

As you can see, this time it has inserted more than 8000 characters.

Conclusion :
Remember, whenever you are planning to insert more than 8000 characters to any varchar column, you must cast it as varchar(max) before insertion.