Limit amount of data returned with the SQL Server TEXTSIZE command

By: Greg Robidoux | Comments (3) | Related: > TSQL

Problem

When working with large-value data types such as varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data types sometimes you do not need to return the complete column contents, but maybe only a portion of the data. This may be for testing or maybe just to check to see if there is a value or not. This can be done by using the SUBSTRING function or the LEFT function, for each column that uses this data type, but are there any other functions that can be used to limit the amount of data returned by a SELECT statement for all columns of these data types?

Solution

SQL Server offers many functions and settings to control individual statements that are issued or overall settings that can be set to control all statements that are issued for a given connection. One of these functions is TEXTSIZE which can limit the amount of data returned for these data types:

varchar(max)
nvarchar(max)
varbinary(max)
text
ntext
image

To set the TEXTSIZE for a given connection the command is issued as follows: SET TEXTSIZE (number)

For example the following will set the text size returned for any of these data types to 2000 characters

SET TEXTSIZE 2000

(note: the default value for the TEXTSIZE is 2,147,483,647)

So let's take a look at a few examples run against the AdventureWorks database.

In this first example we are querying the DocumentSummary column from the Production.Document table. We are not setting the TEXTSIZE, so we are using the default value.

SELECT DocumentSummary FROM Production.Document

The output is shown below.

In this example, we are doing the same query, but setting the TEXTSIZE to 100.

SET TEXTSIZE 100
SELECT DocumentSummary FROM Production.Document

The output is shown below and we can see that the output has been truncated.

This same thing could be done by using the LEFT or SUBSTRING functions as follows. First we resize the TEXTSIZE and then run the commands.

SET TEXTSIZE 50000
SELECT LEFT(DocumentSummary,100) FROM Production.Document
SELECT SUBSTRING(DocumentSummary,1,100) FROM Production.Document

Output from first SELECT

Output from second SELECT

As you can see these functions can do pretty much the same thing, but you need to put the function in front of each column vs. having the TEXTSIZE limit the data for all columns of the data types mentioned above.

Summary

You may have noticed that the size of the text returned in these examples is different then our first example where we set TEXTSIZE = 100. The reason for this is that the column DocumentSummary in the Production.Document table is an nvarchar(max) data type. Data that is stored as unicode takes two bytes for each character vs. non-unicode data. So the actual data that is returned from the first query is only 50 characters, but since it is stored as an nvarchar the first 50 characters takes 100 bytes to store the data. So keep this in mind when you use the TEXTSIZE function.

Also, once the value has been set the entire session for this connection will use this value that has been set. To check what the TEXTSIZE value is you can run the following command.

SELECT @@TEXTSIZE

To set it back to the default value you can establish a new connection or use this command.

SET TEXTSIZE 2147483647

Next Steps

Next time you are doing testing with large data types, remember this command to limit the amount of data returned
Do some testing on your end to see if this is a feature that can aid your development
This should work with all versions of SQL Server. Last tested on SQL Server 2017

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, May 8, 2013 - 4:12:02 PM - Greg Robidoux	Back To Top (23825)
@Paul M - you would need to use a SUBSTRING command for the VARCHAR fields.

Wednesday, May 8, 2013 - 4:05:53 PM - Paul M	Back To Top (23823)
Hello sir. I am having difficulties making set TEXTSIZE n work. It seems it only applies to Text fields and not say, varchar fields. How can I get TEXTSIZE to work on fields other than Text. Thanks!

Tuesday, June 5, 2012 - 6:05:52 PM - MR	Back To Top (17810)
How do you set the default TEXTSIZE value for the SQL Agent (i.e. when jobs are run) to the 2GB value? When a job gets data from a remote (linked) server - it sets the default to 1024 for the SQL Agent. How do we tell it to have the default be 2GB - without having to explicitly set each time?