SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Tom Carnahan
Tom Carnahan
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 1489
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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90275 Visits: 45284
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
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40800 Visits: 38567
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Tom Carnahan
Tom Carnahan
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 1489
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
Tom Carnahan
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 1489
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
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89445 Visits: 41144
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90275 Visits: 45284
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
Tom Carnahan
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 1489
Many thanks to both of you!

-- Tom
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search