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 (http://blog.sqlauthority.com)