January 16, 2008 at 12:30 am
We are facing an issue on the table design for a specific project. The no of columns is 239 and the row size exceeds 8060 bytes. Now, which would be the best option? to split the table and use two insert commands, or an insert comand and a subsequent update, keeping in mind that our functional requirement needs saving of data for few columns in a page, but normalizing the table would result in the ID column being redundant in the other tables. Also, I would like to discuss regarding the performance difference between the two.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
January 16, 2008 at 2:59 am
January 16, 2008 at 6:52 am
Just understand that you can't escape the 8060 limit by doing an insert and an update. If you exceed 8060 in a row, you've exceeded it.
Another option that you might want to explore as a mechanism to avoid this is to use varchar(max) fields. It changes the storage mechanism such that you can put more data in than 8060 without warnings. However, with 250+ fields, you may run into other issues too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 11:02 am
239 columns? You definitely have a problem with normalization. I would highly recommend normalizing this table.
The only reason I could see having this many columns (and this is a stretch), would be for adhoc reporting where you have specifically de-normalized.
You are not going to be able to avoid the maximum row size - not with this many columns.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2008 at 12:16 pm
- the 8060 limiet is for fixed length columns only !(in sql2005)
(and var length columns will be shifted off row if needed with the same impact as for text, image, varchar(max), ...(max) columns)
- IMO the most propre way is to normalize. Sooner or later you'll be confronted with design flaws the put to for bigger issues.
If you can start something new, start it propperly.
Don't worry about an extra join. Make your datamodel near perfect and then only denormalize if actualy needed. (and then denormalize properly)
Having your objects normalized will make them more adaptable !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply