In SQL Server, the SET TEXTSIZE statement specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.

Microsoft advises that text, ntext, and image will be removed in a future version of SQL Server, so you should avoid using these data types in new development work, and plan to modify applications that currently use them to use varchar(max), nvarchar(max), or varbinary(max) instead.

Example 1 – Basic Usage

Here’s an example of setting the TEXTSIZE value.

SET TEXTSIZE 2048;

That sets TEXTSIZE to 2048 bytes.

Example 2 – Check the TEXTSIZE Value

You can check the current TEXTSIZE value with @@TEXTSIZE:

SELECT @@TEXTSIZE AS [Text Size];

Result:

+-------------+
| Text Size   |
|-------------|
| 2048        |
+-------------+

Example 3 – How it Affects the Query Results

Here’s an example that demonstrates how the TEXTSIZE value can affect the results returned in a SELECT query.

First let’s create a table, insert some text, then select it.

USE Test;
CREATE TABLE TextSizeTest (
    varchar50 varchar(50),
    varcharMax varchar(max),
    nvarcharMax nvarchar(max)
    ); 
INSERT INTO TextSizeTest
VALUES (
    'Dragonfruit',
    'Dragonfruit',
    'Dragonfruit'
    );

SELECT * FROM TextSizeTest;

Result (normal):

+-------------+--------------+---------------+
| varchar50   | varcharMax   | nvarcharMax   |
|-------------+--------------+---------------|
| Dragonfruit | Dragonfruit  | Dragonfruit   |
+-------------+--------------+---------------+

This is what we’d normally expect. It returns the full text inside each column because each row of text takes up less than 2048 bytes (which is what I set TEXTSIZE to in the previous example).

But here’s what happens if I reduce the TEXTSIZE value:

SET TEXTSIZE 4;
SELECT * FROM TextSizeTest;

Result:

+-------------+--------------+---------------+
| varchar50   | varcharMax   | nvarcharMax   |
|-------------+--------------+---------------|
| Dragonfruit | Drag         | Dr            |
+-------------+--------------+---------------+

The first column is unaffected, because it’s not a “max” column. What I mean is, it’s a varchar(50) and not varchar(max). The TEXTSIZE option only affects columns that are defined with max.

The second column returns the first four characters. This is because we set the TEXTSIZE value to 4, and the first four characters are using 4 bytes.

The third column only returns the first 2 characters. This is because it’s an nvarchar column. In this case, the first two characters are using 4 bytes.

Example 4 – Reset the Value

One thing to be aware of is that specifying SET TEXTSIZE 0 actually “resets” it to the default value of 4096.

SET TEXTSIZE 0;
SELECT @@TEXTSIZE AS [@@TEXTSIZE];
SELECT * FROM TextSizeTest;

Result:

+--------------+
| @@TEXTSIZE   |
|--------------|
| 4096         |
+--------------+
(1 row affected)
+-------------+--------------+---------------+
| varchar50   | varcharMax   | nvarcharMax   |
|-------------+--------------+---------------|
| Dragonfruit | Dragonfruit  | Dragonfruit   |
+-------------+--------------+---------------+
(1 row affected)