Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

SINGLE_BLOB Size Limit: An Affirmation for Bert

SINGLE_BLOB Size Limit: An Affirmation for Bert

  • Comments 1
  • Likes

Bert left a comment on this post pointing out a limitation of “the other OPENROWSET trick” (the one which loads an XML file into a single XML column):

I tried this, but had an error because my XML file is 6gB. The SINGLE_BLOB seems to be limited to 2gB

Bert is correct; fortunately, this is easily explained.  The Books OnLine entry for OPENROWSET points out that a SINGLE_BLOB is an varbinary(max) construct;  the documentation for varbinary(max) declares its maximum size to be 2gB.

So you’re right, Bert; this “quick and dirty” method will only work if your XML document is less than 2gB in size.  Larger XML documents will need to be loaded into the database through more conventional means.

Thanks for your comment, Bert; now I’ve got two questions for you..  is your 6gB XML document a production sample, or a large document set up for a capacity test?  And if it’s the former, can you comment a bit on the design that produced a document this large?

-wp

Comments
  • 6 GB of XML?!?  I hope and pray, for whomever is forced to work with that thing, that it's actually many, many, many documents, not one.  

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment