Limit amount of data returned with the SQL Server TEXTSIZE command

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


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?


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


(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.

document summary

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

SELECT DocumentSummary FROM Production.Document

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

document summary

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.

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

Output from first SELECT

column name

Output from second SELECT

document id

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.


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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux 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 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?

get free sql tips
agree to terms