Limit amount of data returned with the SQL Server TEXTSIZE command


By:   |   Updated: 2007-12-19   |   Comments (3)   |   Related: More > T-SQL


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.

document summary

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.

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.

SET TEXTSIZE 50000
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.

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


Last Updated: 2007-12-19


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Comments For This Article




Wednesday, May 08, 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 08, 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 05, 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?



download





Recommended Reading

Cursor in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor








get free sql tips
agree to terms


Learn more about SQL Server tools