SET TEXTSIZE – Good or Evil?
August 28, 2012 3 Comments
One of the first things I try do to every morning when I’m at the office (besides getting a lot of coffee to get the engine started), is reading up on blogposts that were posted the night before or when I’m at the office. My goal is to try to learn at least 1 thing every day, by reading a blog post or article.
What SET TEXTSIZE does, is limit the size of the data returned by a SELECT statement. As Pinal describes in his blog post, it could be used as a replacement for the LEFT function on each column you retrieve from the database. But I agree: use it only for test purposes. If used in production, in a query that returns (for example) 5 columns, the SET TEXTSIZE is overlooked much easier then 5 LEFT functions. This reduces the chance that you or your colleagues wonder why the returned column value isn’t shown correctly.
The other remark I need to make, is that it’s interpreted differently by the SQL engine. A few examples of this can be found in the comments of the article Pinal wrote.
But when I used SET TEXTSIZE, I started wondering what this will do to your execution plan. According to MSDN TEXTSIZE is set at execute or run time, and not at parse time. But what does this mean for your execution plan?
To try this out, I created a table, and inserted 10.000 records in that table:
CREATE TABLE RandomData (ID INT IDENTITY(1,1), Col1 VARCHAR(MAX), Col2 VARCHAR(MAX), Col3 VARCHAR(MAX), Col4 VARCHAR(MAX), Col5 VARCHAR(MAX)) INSERT INTO RandomData (Col1, Col2, Col3, Col4, Col5) SELECT REPLICATE('1234567890', 100), REPLICATE('1234567890', 100), REPLICATE('1234567890', 100), REPLICATE('1234567890', 100), REPLICATE('1234567890', 100) GO 10000
Once you've created the table, you can run the "old fashioned" script with the LEFT functions:
SELECT LEFT(Col1, 10), LEFT(Col2, 10), LEFT(Col3, 10), LEFT(Col4, 10), LEFT(Col5, 10) FROM RandomData
If you look at the exection plan, it contains a table scan, Compute Scalar (that computes the new values of each row), and the select of the data. Nothing out of the ordinary I would say.
But if you run the same query with the SET TEXTSIZE, it results in an error:
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, 6).
Unexpected end of file while parsing Name has occurred. Line 1, position 6.
The query actually returns the whole set 10.000 records, and the result is correct. Of every column, only the first 10 characters are returned. So what's happening with the execution plan?
If you use either one of the statements below in your session, you can see that the execution plan is generated without any issues:
SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON
There is a Connect item for this issue, but the SQL Server team decided not to fix it in SQL Server 2008. And looking at my screen, they didn't fix it in SQL Server 2012 either...
So my best guess (without knowing what the actual code does), is that the execution plan XML isn't completely transfered to the client. This is part of the resultset, and thus also delimited because of the SET TEXTSIZE.
So my conclusion would be: don't use SET TEXTSIZE, unless you're absolutely aware that the results you receive are delimited and that visualising your execution plan may cause an error (but only in the SSMS!). The query results are retrieved and shown correctly, but the execution plan XML is causing problems when using a small TEXTSIZE.
But if my conclusions are incorrect, or if I've overlooked something, I'd love to hear your comments on it! So don't hesitate to correct me if necessary! ;)