Maximum allowable table row size of 8060 bytes
-
I'm trying to create a table which contains 1000 columns with a datatype of [money] NULL. The creation of the table will fail everytime complaining about:
Creating or altering table 'gm_corda_LFYdollars' failed because the minimum row size would be 8131, including 131 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
What am I doing wrong here?
-
I don't quite no the INTERNALS but this was observed on SQL Server 7:
http://www.windowsitpro.com/Article/ArticleID/14298/14298.html
see #9 under Ugly (or just different)
------------------------
9. Another reason for tables growing in size is that NULL columns used to take up no space at all, but that is no longer true. So a NULL CHAR(10) now uses 10 bytes rather than 1, a NULL MONEY now uses 8 instead of 1. A NULL varchar will take 0 as before.
Thanks...Michelle
-
Well 991 columns will work just fine. Strangely enough I don't have this problem using [int] NULL as a datatype. As far as I can tell [money] NULL is using 8 bytes per entry. When I only use 991 columns the total < 8060 (991*8+131=8059) so it will work. I'm not sure where the 131 value is coming from, sql says it's "internal overhead" whatever that means.
It's just interesting that [int] NULL will work whereas [money] NULL will not.
-
Personally I find it hard to understand the need for 1000 columns in a table. I generally run out of absolute distinct pieces of information on an object when I hit 20 or 30 attributes.
-
I wish it where that simple.
-
There was a nice article by Steve Jones if you wish to know more about the internal things... you may find interesting info there :
What is the Maximum Page Size in SQL Server 2000?
Among other things, the author found out that the maximum allowable size of row is not 8060 (as stated in the error message), but 8039.
About the table with 1000 columns - just split it into several tables.
-
That's what I have done. 0 to 499 in one and 500 to 999 in another. It's ugly but it works. =)
-
Would it make sense to have a sub-table with the values, so if null, there is no subtable column?
declare table main (id int identity)
declare table main_values(id int identity, main_id int, tag int, value money)
where "tag" replaces your 1,000 field names and main_id is the main table row id.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply