How to store more than 8000 characters in a column in SQL Server 2005?

Question

I have a table named as ABC. In this table I have a column which is defined as XYZ

varchar(8000).

In this column I am storing the SOAP error request.

My problem is that now I am getting the length of SOAP Error request more than

8000 (to be accurate it is now 16000). 

I have read that the maximum length of varchar is 8000, so my question is that how can I increase the length of my column XYZ to 16000?

Answer 1

If you really need more than 8000 characters, you need to use VARCHAR(MAX) which can store up to 2 GB of text:

XYZ varchar(max)

This gives you up to 2 billion characters - which is Leo Tolstoj's War and Peace about 200 times over - should be enough for most cases!

Note: if you get a SOAP request, that most likely will be properly formatted XML - right? In that case, you could also use the XML datatype of SQL Server 2005 and newer. It also stores up to 2 GB of data, but it stores it more efficiently than a plain varchar(max) does - and you can run XPath/XQuery against it to grab bits from it.

So I'd recommend you use:

XYZ XML