Strategies for bypassing the 8,060 byte limit on Row Length

Question

Strategies for bypassing the 8,060 byte limit on Row Length

Tom Carnahan

SSCommitted

Points: 1788
More actions
January 19, 2010 at 1:22 pm

#74583

I am working with an inherited Commercial-off-the-Shelf (COTS) application that uses SS2K. I know from the BOL for SS2K that regardless of the data type, rows are limited to am maximum of 8,060 bytes. I found an article at SqlServerCentral where the author narrowed that down to 8,046 with 14 bytes for overhead.
Our application has a "forum" feature where users can leave messages. Since this application is used in a "language education" environment, the character sets used are not all Latin so the field that contains the body of the message typed NVARCHAR (max 4000 Latin chars).
Now my group has been approached and asked to design a work-around so that the users can exceed the 8,060 byte limit.
In my mind, I can imagine that we would have to come up with a design where the message text can occupy multiple rows in a table. I am thinking that we would have to manage pointers and row continuation flags, etc.
Q1: Has anyone seen a stored procedure or process that would be a suitable workaround?
Q2: If so, did the designers of the system handle the extended row by using code in the front or back-end?
Q3: Can anyone point me to an example on the web that describes such a design or where I can get some advice for this predicament?
Thanks ahead of time for any help you can provide!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Use the NTEXT data type. It's stored out of row and the maximum is somewhere around 2 billion characters.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I see two choices, neither of which I would implement without involving the vendor of the product, unless you own it.

One requires upgrading to SQL Server 2005/2008 and changing the column to NVARCHAR(MAX). The second would be to change the column in SQL Server 2000 to NTEXT, know that this is depreciated in SQL Server 2005 with the new VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types.

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 3

Gail and Lynn,

Thanks for your advice. Today, I went and created a test table with one ntext column and was able to insert 38 million characters ... it took 4 minutes for the insert, but it did it. I was surprised.

Would either of you know what BOL is talking about when it says that for SS2K, the max row length is 8060 bytes? I clearly saw that by using ntext, I could go way beyond that.

Thanks,

Tom

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 4

PS. My organization previously bought the source code to the COTS package that we have and we are able to manipulate the back-end. My thought is to try changing the field we are concerned about from nvarchar to ntext.

Does that seem like a reasonable thing to try?

Thanks,

Tom

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Just a clarification... use of NTEXT in 2k does NOT allow you to violate the 8060 rule. The data that's normally stored in the table is a numeric pointer to the data which is actually "out of row" just like Gail said.

So far as changing the app to use NTEXT instead of NVARCHAR(4000), I don't believe it'll be as easy as you think. There are very few SQL functions that work on NTEXT and you cannot do things like "concatenation" to add to existing data without jumping through the hoop of determining the value of the pointer, etc. In summary, there are very few things that are similar between NTEXT and NVARCHAR. If you do decide to test it, make real sure it's on a test machine because I believe you're in for a bit of work.

I'll also give you a hint about something that may help you work past some of the shortcomings of handling NTEXT and TEXT datatypes... although you cannot explicitly declare an NTEXT or TEXT variable, they can be defined as parameters for a stored procedure. An "easy" way to get around all of the normal T-SQL manipulations for NTEXT is to do it all in the GUI and pass the result back though a proc for final storage (overwritten replacement if necessary).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Tom Carnahan (1/19/2010)
Would either of you know what BOL is talking about when it says that for SS2K, the max row length is 8060 bytes? I clearly saw that by using ntext, I could go way beyond that.

The max row length is 8060. Text, ntext and image columns are stored 'out of row'

It's like saying that only x letters can be written on an A5 page, but you can say "see file cabinet 54 for more info" and 'point' at far more than could be stored on the page.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 7

Tom Carnahan

SSCommitted

Points: 1788

January 20, 2010 at 5:00 am

#1105951

Many thanks to both of you!

-- Tom