May 27, 2005 at 10:34 am
I have a table with a large number of attributes (approx. 260). Logically this information belongs in one table. The problem is that the row size exceeds the maximum bytes per row (8060). There are a number of large varchar attributes and four 'text' attributes. The 'Text' attributes use the 'EXEC sp_tableoption 'echo', 'text in row', '2400'' option. In practice, a single row doesn't approach the maximum (at least not yet). Yet, the 'Warning' messages cause issues during certain operations.
My question, how is this type of situation typically handled? I've resisted breaking the table into two and joining it back together. Anyone out there dealt with this situation? Thanks....
May 27, 2005 at 10:48 am
I've written something that has this warning as well. I only see the warning in Query Analyzer and it does not affect any application. As with yours, I know mine will never reach that limit, but I had to follow some established standards.
Two questions:
What are the issues? Mine were that other people running this code from Query Analyzer, could see this Warning. Clients never see this warning.
Why can't the table be broken up?
I wasn't born stupid - I had to study.
June 1, 2005 at 9:36 am
I ran into this problem and just bit the bullet and broke the table out. It came down to the fact that there was a possibility that the row could exceed the 8000 character limit and if it had done so I would be the one responsible for the data loss. What I did is create two more tables with owning relationships to the main table. This allowed the join statements to made using a single primary key value.
June 1, 2005 at 9:52 am
That's the way I would go to... I used this technique once before. I put all the important columns in the main table, then every other text and large varchar were shipped in another table with the same primary key linking back to the main table. This can be good if you only want to access either of the groups of the columns. Also it might provide a performance boost when you query only the main table because the server won't have as much data to read to get to the data you want.
June 1, 2005 at 10:27 am
An interesting thing to note is that this is solved in SQL Server 2005, using a new allocation unit called overflow data. See more in my blog entry http://www.hedgate.net/blog/2005/04/10/support-for-large-rows/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy