Today’s post is prompted by a question I was asked by one of the fellow team members recently. Most enterprise applications have a “comments” or “memo” field used to store free-form text comments entered by the users, and the product that I work on is no different. Depending upon whether or not Unicode support is required, VARCHAR(MAX) or NVARCHAR(MAX) are the general choice for storage of these fields. As part of a troubleshooting exercise, one of the engineers was attempting to query this data in SSMS for exporting to a text file for further analysis. However, the team member immediately ran into a problem and approached me.

Team member: I am facing an issue with the storage of comments in our database.

Me: What happened? Is the data not being stored as expected?

Team member: No, it appears that the storage is correct, but I am unable to view it in SSMS.

Me (stumped): Okay…..let’s take a look right away.

(We walk over to the team member’s desk to take a look at the issue).

Team member: Here’s the research I have done till now. As you can see, the LEN and DATALENGTH functions show the correct length of the comments entered, but when I execute a SELECT from the table with results in text mode (Ctrl + T), I only get 256 characters in text results mode of SSMS. Can you help me get the complete data?

USE tempdb;
GO
SET NOCOUNT ON;
--Temporary table variable for demonstration of the issue
DECLARE @textModeTest TABLE (RecId INT, RecText VARCHAR(MAX));

--Inserting sample data
INSERT INTO @textModeTest (RecId, RecText) 
VALUES (1, REPLICATE('a',2000));

--Selecting the data lengths and associated data
SELECT LEN(RecText) AS LENValue, 
       DATALENGTH(RecText) AS DATALENGTHValue, 
       RecText
FROM @textModeTest;

--Trying to get the length of the data selected
--To do this, copy the output from the "RecText" column in the query above
--and attempt to fetch the length for that
SELECT LEN('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') 
       AS VisibleTextLength;

Me: Okay, now I understand your confusion. As a matter of fact, the SSMS is working as expected.

Team member: If it’s working as expected, how can I view the actual, i.e. non-truncated value of the column? Are there any alternatives?

Me: Sure! There are not one, but at least two ways in which you can view the non-truncated data. First I will show how to fix up SSMS, and will later share a method that I prefer over SSMS for these purposes.

Method 01: Changing the maximum number of characters displayed

A simple configuration change in the SSMS can fetch upto 8192 (8K) characters when the results are displayed in Text mode (Ctrl + T). To fix this in SSMS:

1. Go to Tools –> Options
2. Navigate out to Query Results –> SQL Server –> Results to Text
3. Change the value under “Maximum number of characters displayed in each column” from 256 to 8192
4. Restart SSMS
5. Next time when the query is executed, one can notice that the SSMS displays upto 8192 characters in the related columns of the result set

Method 02: Using BCP

The method shown above has a major limitation around the number of characters being displayed – they are limited to 8192 characters. Instead of this, I generally use BCP/bulk-copy mechanisms to extract full-length data from the database.

More information about BCP and how to extract information into text files using BCP is available on my historical posts at:

• BCP & Bulk Insert
• Query Result configuration – Generate comma separated output without BCP or query changes

Me: So that’s it! Now you know not one but two methods of viewing and exporting full-length character data in SSMS or any other text file.

Team member: Thank-you so much! I never knew that SSMS was so flexible! A simply configuration change made my day!

Me: You are welcome! I am glad I could help.

Until we meet next time,

Be courteous. Drive responsibly.