Is there any size limit for the values returned from a Stored Procedure in SQL 2005?

Question

I am trying to return Xml value from SQL Server 2005 using For Xml. Is there any limit in the size of xml returned from SQL Server?

I will be using ExecuteXmlReader to get the value in my C# code.

Also, is there any limit on the data that can hold in an XmlReader?

Any thoughts...

Answer 1

No, there's no limit. But it should be obvious that if you return many megabytes of data, the speed of data retrieval will suffer.

Answer 2

There is no practical limit, but you are normally returning the equivalent of nvarchar(MAX), so you're likely to run into a ~2 billion character limit (and probably much less then that because of limited free address space).

However there are no small limits, like the 32KB SQL statement limit, that you need to worry about.

Answer 3

With SQL 2005 and .Net 2.0 Microsoft do not publish a limit, but in practise large-ish result sets can fail with 'out of memory' errors etc

For XML puts a extra load on the DB server, are you sure you wouldn't be better of processing the raw data into XML on an application server or client?

Answer 4

Make sure to use the following code:

while(xmlReader.Read()){ 
...
}

otherwise you might end up having invalid XML if your XML document is too big to fit into one record. SQL Server will spread it up over multiple records so to day,...

Answer 5

Technically there is a 1 Gig limit as the XML datatype is based off of the NVARCHAR(MAX) data type. But if you have a 1 Gig XML document you need to look at making it smaller.