Here is a small conversation I received. I thought, though an old topic, indeed a thought provoking for the moment for SET TEXTSIZE.
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.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
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.
Good Point Manoj,
I meant it for SSMS only but having your comment here complements the original blog post.
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.
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
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
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