SQL SERVER – A Brief Note on SET TEXTSIZE
- by pinaldave
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.
Here is my candid opinion – I do not think they can be directly comparable even though both of them give the exact same result. 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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
