How to Limit Output of Varchar(max), Nvarchar(max) in SELECT Statement? – Interview Question of the Week #218

Question: How to Limit Output of Varchar(max), Nvarchar(max) in SELECT Statement?

Answer: I must admit, I had to do search the internet for this answer. I had no idea how to answer this question. I was initially asked this question during the Comprehensive Database Performance Health Check.

When we write SELECT statement including a Text field it retrieves the entire text field in the SELECT statement. It is fine in most of the case. However, there may be a situation where we do not need the entire column. In that case, we have to limit the VARCHAR(MAX) or NVARCHAR(MAX) column. There is a simple trick to make it happen and it also works on varbinary(max), text, and ntext.

Let us see a small demonstration to see how we can limit the output of MAX columns.

SELECT [Object]
,[TSQL]
FROM [AdventureWorks2014].[dbo].[DatabaseLog];
-- Set text size to 20 characters
SET TEXTSIZE 20;
SELECT [Object]
,[TSQL]
FROM [AdventureWorks2014].[dbo].[DatabaseLog];
-- Reset to unlimited characters
SET TEXTSIZE -1

Now let us see the output of the previous query. You can see once we set the value to SET TEXTSIZE to 20 to the only column which is affected are the column of datatype VARCHAR(MAX). From the keywords SET TEXTSIZE other columns are not affected.

Let me know if there are other such tricky commands available in SQL Server which are not commonly known but are interesting to learn about.

Reference: Pinal Dave (https://blog.sqlauthority.com)