Varchar(max) not storing all data

Question

Varchar(max) not storing all data

bindish

SSC-Addicted

Points: 471
More actions
September 4, 2012 at 11:57 am

#261123

Hi,
I have one table with one field called "vLargeData varchar(max)" datatype. I am inserting data into table and that data size is 115000. After inserting when I am checking data then it gives me only 43680 character. Why?
I checked in Microsoft document and it says you can store 2^31 but it is storing only 43680 character. Why?
Any help would be appreciated.
Thanks
Bindish Thakkar

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

bindish (9/4/2012)
Hi,
I have one table with one field called "vLargeData varchar(max)" datatype. I am inserting data into table and that data size is 115000. After inserting when I am checking data then it gives me only 43680 character. Why?
I checked in Microsoft document and it says you can store 2^31 but it is storing only 43680 character. Why?
Any help would be appreciated.
Thanks
Bindish Thakkar

You data is there. Why do you say it is only 43,680 characters?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bindish SSC-Addicted Points: 471 More actions · Answer 2

Yes data is there but its only 43600 characters. But the total size is 115000 characters.

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

bindish (9/4/2012)
Yes data is there but its only 43600 characters. But the total size is 115000 characters.

So how do you know there is only 43,600 characters? Are you looking at this in SSMS? SSMS will not display all the text in a varchar(max) field but the text really is in the column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323580 More actions · Answer 4

bindish (9/4/2012)
Yes data is there but its only 43600 characters. But the total size is 115000 characters.

how are you checking the data? are you doing select len(YourVarcharColumn) From SomeTable? How are you determining it's only 43600 chars?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bindish SSC-Addicted Points: 471 More actions · Answer 5

I am checking in SSMS . In ch it shows 43600 characters.

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

bindish (9/4/2012)
I am checking in SSMS . In ch it shows 43600 characters.

That is why you can't see all the data. There is an option to increase this but still only to 65k and change. SSMS limits the amount of data it will return so the GUI doesn't get overwhelmed with mountains of data. As Lowell said, check LEN(YourColumn). If you are still in doubt put together quick application with a textarea/textbox and bind it to your field. Or even use SSIS to spit it out to file.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bindish SSC-Addicted Points: 471 More actions · Answer 7

I also change datatype to text and check the size. It still shows me 43600 characters in SSMS.

Sean Lange SSC Guru Points: 286609 More actions · Answer 8

bindish (9/4/2012)
I also change datatype to text and check the size. It still shows me 43600 characters in SSMS.

I am not sure how else I can say this....SSMS does NOT show all the characters.

Tools -> Options -> Query Results -> SQL Server -> Results to Grid

This setting allows you to set the maximum displayed characters for Non XML data. It has an upper limit of 65,535 characters.

As Lowell and I have said before, try using a query LEN(YourColumn).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bindish SSC-Addicted Points: 471 More actions · Answer 9

You are right as it is storing all data into table and checked with LEN command but in SSMS it is now showing. There is limit to 65535.

Is there any way to see this large data(115000 char) in query?

Thanks,

Bindish

Lowell SSC Guru Points: 323580 More actions · Answer 10

bindish (9/4/2012)
You are right as it is storing all data into table and checked with LEN command but in SSMS it is now showing. There is limit to 65535.
Is there any way to see this large data(115000 char) in query?
Thanks,
Bindish

not with SSMS, no.

There is no way to see all the data in SSMS; it is designed more as a develoepr tool,a nd not as the be-all-end-all Reporting module.

it limits the data presented to conserve resources.

if you need to see it, you can always grab it in multiple pieces with substring.

Otherwise, You'll need use some other tool, as Sean suggested; a web page, SSIS, to export it out.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 11

You can output is as XML in grid view so it will appear as a link:

SELECT YourColumnName as [processing-instruction(x)]

FROM YourSchemaName.YourTableName

FOR XML PATH('');

Then click the link to see the entire text.

The column alias is the secret sauce. It seems you can replace 'x' with anything you want but you must use the form processing-instruction(x)

Credit to Adam Machanic on this technique. See workarounds: Connect > SSMS - Allow large text to be displayed in as a link

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Sean Lange SSC Guru Points: 286609 More actions · Answer 12

opc.three (9/4/2012)
You can output is as XML in grid view so it will appear as a link:
SELECT YourColumnName as [processing-instruction(x)]
FROM YourSchemaName.YourTableName
FOR XML PATH('');
Then click the link to see the entire text.
The column alias is the secret sauce. It seems you can replace 'x' with anything you want but you must use the form processing-instruction(x)
Credit to Adam Machanic on this technique. See workarounds: Connect > SSMS - Allow large text to be displayed in as a link

That is a pretty cool work around. Thanks for the tip. I had a total breakthrough reading the comments on his blog post about the WhoISActive. Never thought of the concept of a temp procedure before. That is pretty cool. Will make working on some forum issues a lot simpler. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/