By: Greg Robidoux | Updated: 2007-12-19 | Comments (3) | T-SQL
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:
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.
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 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
About the author
View all my tips