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 153 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

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 608 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

Share your thoughts...

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