SQL SERVER – A Brief Note on SET TEXTSIZE

Here is a small conversation I received. I thought though an old topic, indeed a thought provoking for the moment.

Question: Is there any difference between LEFT function and SET TEXTSIZE?

I really like this small but interesting question. The question does not specify the difference between usage or performance. Anyway we will quickly take a look at how TEXTSIZE works.

You can run the following script to see how LEFT and SET TEXTSIZE works.

USE TempDB
GO
-- Create TestTable
CREATE TABLE MyTable (ID INT, MyText VARCHAR(MAX))
GO
INSERT MyTable (ID, MyText)
VALUES(1, REPLICATE('1234567890', 100))
GO
-- Select Data
SELECT ID, MyText
FROM MyTable
GO
-- Using Left
SELECT ID, LEFT(MyText, 10) MyText
FROM MyTable
GO
-- Set TextSize
SET TEXTSIZE 10;
SELECT ID, MyText
FROM MyTable;
SET TEXTSIZE 2147483647
GO
-- Clean up
DROP TABLE MyTable
GO

Now let us see the usage result which we receive from both of the example.

If you are going to ask what you should do – I really do not know. I can tell you where I will use either of the same. LEFT seems to be easy to use but again if you like to do extra work related to SET TEXTSIZE go for it. Here is how I will use SET TEXTSIZE. If I am selecting data from in my SSMS for testing or any other non production related work from a large table which has lots of columns with varchar data, I will consider using this statement to reduce the amount of the data which is retrieved in the result set. In simple word, for testing purpose I will use it. On the production server, there should be a specific reason to use the same. (Please note this is applicable to SSMS only, server side the behavior is very different.)

Here is my candid opinion – I do not think they can be directly comparable even though both of them give the exact same result while using SSMS. LEFT is applicable only on the column of a single SELECT statement. where it is used but it SET TEXTSIZE applies to all the columns in the SELECT and follow up SELECT statements till the SET TEXTSIZE is not modified again in the session. Uncomparable!

I hope this sample example gives you idea how to use SET TEXTSIZE in your daily use.

I would like to know your opinion about how and when do you use this feature. Please leave a comment.

Excellent follow up blog post SET TEXTSIZE – Good or Evil?

Reference: Pinal Dave (http://blog.sqlauthority.com)

8 thoughts on “SQL SERVER – A Brief Note on SET TEXTSIZE

  1. Hi Pinal,

    Both of them are not same and do not give same result.

    They are different only for the SSMS text editor, but not for the Query Engine.

    Check this:

    — Using Left
    SELECT ID, LEFT(MyText, 10) MyText
    into #temp1
    FROM MyTable
    GO

    — Set TextSize
    SET TEXTSIZE 10;
    SELECT ID, MyText
    into #temp2
    FROM MyTable;
    SET TEXTSIZE 2147483647
    GO

    select * from #temp1
    select * from #temp2
    GO

    You won’t get same number of character in both the table’s columns.

    Like

  2. Hi Pinal,
    Just similar question I have related to difference between using TOP and SET ROWCOUNT. Even if they give similar result is there any difference in performance.

    Like

  3. In addition what Pinal said, the only difference between ‘TEXTSIZE’ and ‘LEFT’ i can think of is that you can query ‘TEXTSIZE’ with ‘select @@TEXTSIZE’ where you can retrieve the integer value of the global variable.

    Maybe you can use it for stored procs and such. I usually don’t use ‘TEXTSIZE’.

    Cheers,
    Daniel

    Like

  4. Hi Pinal,

    When we use TEXTSIZE in a session, this is considering only “n” bytes of each column with datatype is related to varchar or nvarchar.

    below query show the same.
    and when i try to use execution plan, its throws exception like …

    An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:
    There is an error in XML document (1, 1).
    Data at the root level is invalid. Line 1, position 1.

    i guess , the xml document is picking up first five characters.
    correct me if am wrong view..

    USE TempDB
    GO
    — Create TestTable
    CREATE TABLE MyTable (ID INT, MyText VARCHAR(MAX),newtext nvarchar(max))
    GO
    INSERT MyTable (ID, MyText, newtext )
    VALUES(1, REPLICATE(‘1234567890′, 100), REPLICATE(‘1234567890′, 100))
    GO
    — Select Data
    SELECT ID, MyText
    FROM MyTable
    GO
    — Using Left
    SELECT ID, LEFT(MyText, 10) MyText
    FROM MyTable
    GO
    — Set TextSize
    SET TEXTSIZE 5;
    SELECT ID, MyText,newtext
    FROM MyTable;
    SET TEXTSIZE 2147483647
    GO
    — Clean up
    DROP TABLE MyTable
    GO

    Like

  5. USE TempDB
    GO
    — Create TestTable
    CREATE TABLE MyTable (ID INT, MyText VARCHAR(MAX),newtext nvarchar(max))
    GO
    INSERT MyTable (ID, MyText, newtext )
    VALUES(1, REPLICATE(‘1234567890′, 100), REPLICATE(‘1234567890′, 100))
    GO
    — Select Data
    SELECT ID, MyText
    FROM MyTable
    GO
    — Using Left
    SELECT ID, LEFT(MyText, 5) MyText,LEFT(NEWTEXT,5)newtext
    FROM MyTable
    GO
    — Set TextSize
    SET TEXTSIZE 5;
    SELECT ID, MyText,newtext
    FROM MyTable;
    SET TEXTSIZE 2147483647
    GO
    — Clean up
    DROP TABLE MyTable
    GO

    Like

  6. Pingback: SET TEXTSIZE – Good or Evil? « SQL from the Trenches

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s