You may be familiar with the SET TEXTSIZE statement in SQL Server, that enables you to limit the amount of data returned in a SELECT query.

Perhaps you’re finding that its results are exactly the same as LEFT() when running a specific query. Which begs the question: Is there a difference between these two options?

There are definitely differences between these two options. There are cases where both functions will return completely different results. And there are also differences in how they work, as well as the type of data that they can be used on.

Definitions

Let’s start by looking at what each one does.

LEFT()

Returns the left part of a character string with the specified number of characters.

SET TEXTSIZE

Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.

Let’s examine this in detail.

Characters vs Data Size

Pay attention to the wording of each definition. In particular, the words “characters” vs “size” and “data”.

• LEFT() enables you to explicitly state how many characters are returned.
• SET TEXTSIZE enables you to specify the size of the data returned.

This is an important distinction to make, because you’ll get different results depending on the data type. Different characters can require different storage size. One character might use 1 byte and another might use 2 bytes.

Using LEFT() will allow you to specify the number of characters, regardless of how many bytes they use.

SET TEXTSIZE on the other hand, allows you to specify the number of bytes to return – not the number characters.

If the data is stored using nvarchar(max) for example, you might find that SET TEXTSIZE returns half the number of characters that LEFT() does.

Example:

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

SET TEXTSIZE 50;
SELECT 
  LEFT(varchar50, 4) AS varchar50,
  LEFT(varcharMax, 4) AS varcharMax,
  LEFT(nvarcharMax, 4) AS nvarcharMax
FROM TextSizeTest;

SET TEXTSIZE 4;
SELECT * FROM TextSizeTest;

Result:

+-------------+--------------+---------------+
| varchar50   | varcharMax   | nvarcharMax   |
|-------------+--------------+---------------|
| Drag        | Drag         | Drag          |
+-------------+--------------+---------------+
(1 row affected)
Commands completed successfully.
+-------------+--------------+---------------+
| varchar50   | varcharMax   | nvarcharMax   |
|-------------+--------------+---------------|
| Dragonfruit | Drag         | Dr            |
+-------------+--------------+---------------+
(1 row affected)

The first result is for LEFT(). It works across all three data types and sizes. It returns the exact number of characters specified.

The second result is for SET TEXTSIZE. It doesn’t affect the varchar(50) results. It only affects the other two. But it affects them differently. For varchar(max), each character uses 1 byte, and so we get the same number of characters returned as the specified TEXTSIZE. For nvarchar(max) it’s a different story. Each character uses 2 bytes, and so we only get half the number of characters returned.

However, the results returned by LEFT() could still potentially be affected by SET TEXTSIZE. If I run the LEFT() query again, but precede it with SET TEXTSIZE 2, we get this:

SET TEXTSIZE 2;
SELECT 
  LEFT(varchar50, 4) AS varchar50,
  LEFT(varcharMax, 4) AS varcharMax,
  LEFT(nvarcharMax, 4) AS nvarcharMax
FROM TextSizeTest;

Result:

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

Max Data Size vs Fixed Data Size

Another difference is that SET TEXTSIZE only works on varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data. Note the max part. If you use a fixed data size (such as varchar(255) for example), SET TEXTSIZE won’t work.

LEFT() on the other hand, works with all character data except except text and ntext.

You can see these differences in the previous example. The varchar(50) data was affected by LEFT() but not SET TEXTSIZE.

LEFT() is More Specific

Another difference is that LEFT() is only applied to the column (or constant or variable) you specify. If you want to apply it to multiple columns/expressions, you need to include it again for each expression. Same with subsequent queries.

SET TEXTSIZE on the other hand, is applied to all applicable columns returned in the query. It’s also applied to all queries until it’s set to another value.