Get all the data from a VARCHAR(MAX) or Text Column

Often when I am working with large amounts of content stored in a database, there will be more than 2000 characters. Many SQL Server databases have varchar(max) columns which will store large amounts of content, but SQL Server Management Studio (SSMS) will only return the first 2000. In order to get all the data from a VARCHAR(MAX) or Text Column, you can’t use the default grid result pane of SSMS. In order to return all the data from a varchar(max) column, the best thing to do it to use XML Path.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
DECLARE @S varchar(max)
SELECT @S = ''
SELECT @S = @S + LargeTextColumnStoredAsVarchar FROM dbo.MyTableNameHere
SELECT @S AS [processing-instruction(x)] FOR XML PATH('')
ROLLBACK

The result will be the full content from LargeTextColumnStoredAsVarchar.

I have been personally impacted by this as I copied the content of a result set from SQL Server Management Studio (SSMS) that I assumed was all the data, but in fact, it was only a small part. Next time, remember to use XML Path to get all the content.