Like This Blog 0

Added by Peter Avila February 13, 2013

My students sometimes ask me how they might limit the amount of text displayed in large text columns, like nvarchar(max), for example. Data types like nvarchar(max), varchar(max), varbinary(max), text, ntext, and image (the last three are deprecated and should not be used for new development) can store very large amounts of data that are not always needed. For example, a report might not need to display the entire contents of a nvarchar(max) column—maybe just the first 100 characters. This being the case, it is a waste to have a Select statement return so much data that isn’t needed. That only forces the system to have to handle it, move it around, set it up for display, and so on.

There are a couple of ways to limit text data returned by a Select statement. One is to use string functions, like the LEFT(), RIGHT() and SUBSTRING() functions, to return only a subset of the text. This does the job but, as we’ll see, can be impractical. Another approach is to use the TEXTSIZE setting. Let’s look at an example.

The Production.Document table in the AdventureWorks20012 database has a column, DocumentSummary, that can hold large amounts of text; its data type is nvarchar(max). Let’s display that column:

Notice the ellipses (…) at the end of the non-NULL rows. They indicate that there is more text in the field that is not being displayed because of the limited width of the display column. If we don’t need all that data, we can limit the amount of data returned by the Select statement.

One way to do this is to use the LEFT() function. In the following example, we limit the data to the first 100 characters only:

Notice that the ellipses are gone, indicating that what we see is all there is. This is better, but we are lucky we have only one nvarchar(max) column to display. If we had more, those columns would each also have to make a call to the LEFT function. Function calls can be resource intensive. A better way is to set the TEXTSIZE setting that then applies to all large-data column types.

Notice that we got 50 characters instead of the 100 we might have been expecting. That’s because the column type is nvarchar (Unicode) as opposed to varchar. Unicode uses 2 bytes per character. We can see that the TEXTSIZE statement limits the data to a certain number of bytes (in this case, 100), not characters! It’s a good idea to keep this in mind when using TEXTSIZE. If the data type of our Document Summary column were varchar(max), instead, then setting TEXTSIZE to 100 would have gotten us the first 100 characters. As it is, to get the 100 characters we originally wanted from our nvarchar(max) column, we have to set the TEXTSIZE to 200:

And now our results look just like the one we got when we used the LEFT() function.

Other Things to Keep in Mind When Using TEXTSIZE

TEXTSIZE limits the amount of text returned by the Select statement for these data types:

• Varchar(max)
• NVarchar(max)
• Varbinary(max)
• Text (deprecated)
• NText (deprecated)
• Image (deprecated)

Use the @@TEXTSIZE niladic function to find out what the current TEXTSIZE setting is:

You can find the default value of the TEXTSIZE setting by opening a new Query window in SQL Server Management Studio and querying the @@TextSize function. In SQL Server 2008, 2008 R2, and 2012 the default TEXTSIZE setting is 2,147,483,647.

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ