TRUNCATED Column value at MS SQL 2008

Question

TRUNCATED Column value at MS SQL 2008

subscriptionemail

SSC-Addicted

Points: 426
More actions
September 9, 2015 at 3:18 am

#399008

Hi!
I just have a question regarding storing values to a column in ms sql 2008.
Why is it that the value I inserted at the column is truncated when selected in a query.
The column for this is created to accept max. values.
-> Message VARCHAR(MAX) NULL
The string which I need to insert is a combination of characters with a length of 14,720.
I have been researching at the net and even forums to resolve this.
According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)
Kindly advise!
Thank you very much.

Viewing 8 posts - 1 through 7 (of 7 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

MAX columns accept data up to 2GB of text.

If the text is truncated, it may be due to some function you're applying to the data before inserting. For instance, REPLICATE is limited to 8000 chars.

Another possibility is that you're not seeing the whole text in your column due to client settings. Try selecting LEN(column) or DATALENGTH(column) to see how many characters are actually stored in there.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Probably because of your Management Studio settings.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

subscriptionemail SSC-Addicted Points: 426 More actions · Answer 3

Yes, the string is inserted at the column correctly, therefore the value is not truncated the moment

I insert it to column. However, when I select the column (select column_name from table) the display of the value is not shown completely.

Can I adjust the setting of the display of column so that everything inserted will be shown?

Thanks a lot.

subscriptionemail SSC-Addicted Points: 426 More actions · Answer 4

Hi,

Is there a way to adjust the value for that?

Let's say I need to increase it up to 100,000?

Thanks a lot.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

subscriptionemail (9/9/2015)
Hi,
Is there a way to adjust the value for that?
Let's say I need to increase it up to 100,000?
Thanks a lot.

No you can't set it to 100,000 in SSMS. If you run a select statement the data is still there it just doesn't display it all in SSMS because 100,000 characters is ridiculous in a grid.

_______________________________________________________________

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/

subscriptionemail SSC-Addicted Points: 426 More actions · Answer 6

😀 yes, I have realized that it is really ridiculous, and a customer will not give a lot of time to read everything at the column 😉

Anyway, I will just review the length of the serialized data inserted to the column and see if the max. is already enough.

Thanks guys for all the answers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

subscriptionemail (9/9/2015)
😀 yes, I have realized that it is really ridiculous, and a customer will not give a lot of time to read everything at the column 😉
Anyway, I will just review the length of the serialized data inserted to the column and see if the max. is already enough.
Thanks guys for all the answers.

The data is in the column. It just doesn't display in SSMS. You have to remember that SSMS is just a client tool for the database and that is a limitation of that piece of software.

I assume your customer is not reading this in SSMS so just make sure that whatever front end you have will let them read it. 😀

_______________________________________________________________

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/