Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Limit amount of data returned with the SQL Server TEXTSIZE command


By:   |   Last Updated: 2007-12-19   |   Comments (3)   |   Related Tips: 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 08, 2013 - 4:12:02 PM - Greg Robidoux Back To Top

@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

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

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?


Learn more about SQL Server tools