3

The suspect table has 299 nvarchar(max) columns, 1 integer column and 1 nvarchar(255) column. Please do not ask me why, this is 3rd party vendor database. Anyway, according to my math, in the worst case scenario we should be well within 8060 limitation:

24x299 + 4 + 510 = 7690

Insert from external source fails with the above error during import process using vendor's import tool. Am I missing something in my calculation? Where possibly 11842 might come from? Any ideas?

Thank you!

Eternum
  • 115
  • 2
  • 6

1 Answers1

6

This is down to the implementation of how VARCHAR(MAX) and NVARCHAR(MAX) actually store their data. If the number of bytes needing to be stored per column is less than or equal to 8000 bytes then the data will be stored in-row, if it is greater than 8000 bytes then it will be stored in LOB pages. When this happens, the the row holds a pointer to the LOB page that actually contains the data.

So, working backwards, the average length of data attempting to be stored stored in each column is:

(11842 - (4 + 510)) / 299 = 38 (approximate bytes / 19 characters)

As the value for each column is underneath the 8000 byte limit, it will attempt to store the data in-row and therefore exceed the limit for a page.

You can check out sys.dm_db_index_physical_stats to find out how much is actually stored in-row vs in LOB pages. Also, SQL Hints has a decent article that covers the basics.

Since you're probably stuck with this vendor database, and the data being imported, you can force off-row storage for all nvarchar(max) columns in the table with: 

EXEC sp_tableoption 'dbo.mytable', 'large value types out of row', 1;
Josh Darnell
  • 29,228
  • 5
  • 65
  • 120
Mr.Brownstone
  • 13,102
  • 4
  • 36
  • 54
  • 3
    You can force off-row storage for nvarchar(max) with EXEC sp_tableoption 'dbo.mytable', 'large value types out of row', 1; – David Browne - Microsoft Jan 11 '20 at 00:50
  • I suspect this option is not enabled for the OP but I should have added that as a possible solution, thank you! – Mr.Brownstone Jan 11 '20 at 00:59
  • Okay, I see. Obviously I had a misconception in my mind. I thought that nvarchar(max) is always stored in LOB pages and row data always stores 24 bytes pointer. Now the error kinda makes sense. I am going to try sp_tableoption as suggested and see if that fixes the issue. – Eternum Jan 11 '20 at 02:12