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


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...


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.


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.


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,...


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.


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?


If you are seeing your results in SQL Server Management Studio, there is an option to set how much data it can display, and it defaults to only 2MB.

To change it, follow this path:
Tools > Options > Query Results > SQL Server > Results to Grid > Maximum Characters Retrieved


SQL Server -> XML, then C# translating text into XML. Not very efficient there.

Pull back the data normal. Use a data reader. Then you can get that into a translatable format. What is the consumer of this data? Do they really need XML? Do they need a regular dump of data?

Alternatively, you could use a DataSet. You might have a good case for a DataSet here. You can save that as XML and save it or send it. A DataSet is designed to hold a bunch of table data.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »