Have you used SET TEXTSIZE to limit the data returned by a query, but found that it just doesn’t seem to work? You’re finding that your SELECT queries continue to return the full amount of text, regardless of the TEXTSIZE value?

Read on. You could be making this one obvious mistake.

When I first encountered the TEXTSIZE option, I decided to run a quick test. But it didn’t work. I tried several times with various TEXTSIZE values, but nothing seemed to work.

“Why isn’t it working?” I wondered.

But then it clicked. I re-read the documentation and realised that I wasn’t using the correct string size for my data types.

The TEXTSIZE only works on the following data types:

• varchar(max)
• nvarchar(max)
• varbinary(max)
• text
• ntext
• image

If you look at the first three, notice they use max for the argument. This means that they use the maximum string/storage size.

I was testing it against columns of type nvarchar(100), varchar(255), etc. No wonder it wasn’t working.

Example

Here’s an example to demonstrate what I mean.

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

SET TEXTSIZE 4;
SELECT * FROM TextSizeTest;

Result:

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

Here, I set the TEXTSIZE value to 4, but the full text of the varchar(50) column remains intact. This column is unaffected by SET TEXTSIZE. And that’s exactly how it’s supposed to work.

It’s only the other two columns that are affected, because they are varchar(max) and nvarchar(max) respectively.

Also, the reason the nvarchar column is shorter than the varchar is because it uses two bytes for each character (compared to varchar‘s one byte per character).

So if you’re encountering problems with SET TEXTSIZE, check your data type.