Few days ago, I was stuck in a problem to retrieving data from “varchar(max)” column. The data in the column was more than 30k characters. I realized that we can not retrieve more than 65535 characters from the column using this “regular” statement:
Select * from TableA
See how it works in sql:
In order to retrieve values we need to use the XML statements. Using XML statements we can retrieve unlimited data from the column (See Tools->Options->Results->Grid-> “XML Data”).
The statement will look like:
Select * from tableA for xml path -- Here we can only specify some column name as well.
Lets see the difference:
Now click on the result. It will show data in a separate tab under SQL. (If the result has large number of characters it may take few seconds to load)
Same will be the case if you have nvarchar(max) column.
I hope it is clear and it helps!
Awaited for your feedbacks and suggestions….!