TEXTSIZE

Example

SET TEXTSIZE 10000;
DECLARE @LongText VARCHAR(MAX) = REPLICATE('A', 15000);
SELECT LEN(@LongText) AS BeforeTextSize, DATALENGTH(@LongText) AS BeforeDataLength;
SET TEXTSIZE 5000;
SELECT LEN(@LongText) AS AfterTextSize, DATALENGTH(@LongText) AS AfterDataLength;

Output

BeforeTextSize   BeforeDataLength
15000            15000

AfterTextSize    AfterDataLength
5000             5000

Explanation

In the example, SET TEXTSIZE 10000; sets the limit for the amount of text data returned to 10000 characters. The variable @LongText is declared and contains 15000 ‘A’s.

SELECT LEN(@LongText) AS BeforeTextSize, DATALENGTH(@LongText) AS BeforeDataLength; returns the length and data length of @LongText before setting the TEXTSIZE to 5000.

After changing the TEXTSIZE to 5000 with SET TEXTSIZE 5000;, resulting @LongText size and data length are adjusted and limited to 5000. Thus SELECT LEN(@LongText) AS AfterTextSize, DATALENGTH(@LongText) AS AfterDataLength; returns 5000 for both. The TEXTSIZE option limits the size of VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, or IMAGE data that a SELECT statement returns.