{"id":246,"date":"2012-06-07T07:45:02","date_gmt":"2012-06-07T07:45:02","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/06\/07\/sql-server-misconception-varcharmax-cannot-store-more-than-8000-characters\/"},"modified":"2012-06-07T07:45:02","modified_gmt":"2012-06-07T07:45:02","slug":"sql-server-misconception-varcharmax-cannot-store-more-than-8000-characters","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/06\/07\/sql-server-misconception-varcharmax-cannot-store-more-than-8000-characters\/","title":{"rendered":"SQL Server Misconception – VARCHAR(MAX) Cannot Store More Than 8000 Characters"},"content":{"rendered":"

This post deals with one of the SQL Server Misconception – VARCHAR(MAX) Cannot Store More Than 8000 Characters.<\/em><\/p>\n

Lets analyse this first<\/em>
\nTake the below query.
\n[sql]
\nDECLARE @String VARCHAR(MAX)
\nDECLARE @i INT
\nSELECT @i = 10000,@String=”<\/p>\n

WHILE @i>0
\nBEGIN
\nSELECT @String = @String + ‘A’
\nSET @i = @i – 1
\nEND<\/p>\n

PRINT @String
\n[\/sql]<\/p>\n

When you run this query you will get the following output.
\nOutput:<\/em><\/p>\n

\"\"<\/a>
Print Output Length<\/figcaption><\/figure>\n

Find the length of the output:<\/em>
\nWhen you move the cursor to the end of the output in the Messages window you can notice that the column number (as shown in above image) to be 8001(So length is 8000 characters).<\/p>\n

This leads to the misconception<\/em>
\n– VARCHAR(MAX) Cannot Store More Than 8000 Characters<\/p>\n

Actuality:<\/strong>
\n– Print statement has a limit of 8000 characters(non-unicode) hence the output is truncated.
\n– The varchar(max) statement can store up to 2gb of data(2^31-1 bytes).<\/p>\n

Take the below SQL Code<\/em>
\n[sql]
\nDECLARE @String nVARCHAR(MAX)
\nDECLARE @i INT
\nSELECT @i = 10000,@String=”<\/p>\n

WHILE @i>0
\nBEGIN
\nSELECT @String = @String + ‘A’
\nSET @i = @i – 1
\nEND<\/p>\n

SELECT LEN(@String) as Length
\n[\/sql]<\/p>\n

Output:<\/em>
\nLength
\n——————–
\n10000<\/em><\/p>\n


\n<\/em>(1 row(s) affected)<\/em><\/p>\n

LEN statement returns 10000 and it proves that varchar(max) can store more than 8000 characters.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

This post deals with one of the SQL Server Misconception – VARCHAR(MAX) Cannot Store More Than 8000 Characters. Lets analyse this first Take the below query. [sql] DECLARE @String VARCHAR(MAX) DECLARE @i INT SELECT @i = 10000,@String=” WHILE @i>0 BEGIN SELECT @String = @String + ‘A’ SET @i = @i – 1 END PRINT @String…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2139,3],"tags":[2140,2141,2142],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/246"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=246"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/246\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=246"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}