Using TEXTSIZE to limit the amount of text displayed by the SQL Select statement

Home > Blogs > SQL Server > Using TEXTSIZE to limit the amount of text displayed by the SQL Select statement

Using TEXTSIZE to limit the amount of text displayed by the SQL Select statement

Like This Blog 0 Peter Avila
Added by 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

Videos You May Like

Agile Methodology in Project Management

0 184 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

Creating Users and Managing Passwords in Microsoft Office 365

0 786 4

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.   For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365    

Using Navigation Controls in a Collaboration Site in SharePoint

0 360 1

In this SharePoint training video, I want to talk about the Navigation Controls in SharePoint. They tend to fall into two kind of different categories; one with the navigation controls in a typical Collaboration Site such as a Team Site or a Project Site. These are Sites that are based on the Team Site Template … Continue reading Using Navigation Controls in a Collaboration Site in SharePoint

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.