Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strategies for bypassing the 8,060 byte limit on Row Length Expand / Collapse
Author
Message
Posted Tuesday, January 19, 2010 1:22 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 5, 2014 9:28 PM
Points: 69, Visits: 1,457
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!

Post #850095
Posted Tuesday, January 19, 2010 1:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #850100
Posted Tuesday, January 19, 2010 1:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 23,296, Visits: 32,034
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.



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)
Post #850101
Posted Tuesday, January 19, 2010 6:48 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 5, 2014 9:28 PM
Points: 69, Visits: 1,457
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
Post #850176
Posted Tuesday, January 19, 2010 6:50 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 5, 2014 9:28 PM
Points: 69, Visits: 1,457
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
Post #850177
Posted Tuesday, January 19, 2010 7:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #850179
Posted Wednesday, January 20, 2010 12:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #850254
Posted Wednesday, January 20, 2010 5:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 5, 2014 9:28 PM
Points: 69, Visits: 1,457
Many thanks to both of you!

-- Tom
Post #850376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse