why varchar(max) is not storing data more than 800

Question

I want to store a string more than 8000 characters so I used VARCHAR(MAX) because its limit is 2gb.

please correct me if I am wrong Varchar(max) stores data in IN_ROW_DATA pages if the string is less than or equal to 8000. If the string is greater than 8000 then it starts storing the string in in LOB_DATA pages and pointer is stored in IN_ROW_DATA pages.

My code below is not storing the string greater than 8000 characters.

create table test(name varchar(max))
insert into test
values(replicate('a',8001)

Select len(name) from test  

-- This will still give me length of 8000 and not 8001. So in this case there is no difference between varchar(max) and varchar(8000).

Can someone tell me what am I doing wrong and how to store string greater than 8000 characters.

Answer 1

You are truncating the data to 8000 bytes before inserting it. To create a value longer than 8000 characters you must use varchar(max) data type, whereas the type of 'a' is just varchar.

create table test(name varchar(max))
insert into test
values(replicate(CAST('a' AS varchar(MAX)), 100000));

select len(name) from test;