Home > SQL Server > Display limited content from the character or binary data column – SET TEXTSIZE – SET Statement – SQL SERVER

Display limited content from the character or binary data column – SET TEXTSIZE – SET Statement – SQL SERVER


SQL Server provides massive data storage types such as varchar(max), nvarchar(max), varbinary(max), text, ntext and image. Using these data types we can store up to 2 GB data in a column. This is not surprising anyone because SQL Server is a sophisticated database management system. But, if we want to write a select statement with these massive columns than SQL query engine may take some resources to display the content. But, there is a way to restrict or limit the current session to display few characters in the output.

SET TEXTSIZE : this set statement is used to specify the size of data to be display in the current session.

this set statement affects the columns that has data types in this list (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image).

Just as a reminder, the data types ntext , text, and image will be removed from the future version of SQL Server.

How to use?

SET TEXTSIZE  number

number is an integer value to represent number of bytes starts from 0 to 2 GB (2,147,483,647 bytes). But, if we give 0 textsize  than SQL Server converts the value to 4 kilo bytes.

Example

1. Display the nvarchar(max) column from AdventureWorks database with default TEXTSIZE setting.

pic1SQL Server displayed the whole content in the column.

2. Setting TEXTSIZE value to 15 bytes for the current session.

pic2In the above example, we have two SQL statements that displays the content of the column documentsummary.  Statement one displayed only 15 characters because TEXTSIZE is set up to display only 15 characters. But, the second statement is using LEFT string function to get 50 characters from the same column. But, the output of both SQL statements are same. Because, TEXTSIZE set statement affected both SQL statements to display only 15 characters for the current session.

Note: LEFT string function should not be used to process binary data column.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: