SQL error while create very big table and inserting data into table

Question

Post reply

SQL error while create very big table and inserting data into table

gvrsony

SSC Veteran

Points: 299
More actions
September 29, 2011 at 10:58 am

#258143

Hello,
When i am trying to create a table with 350 columns with each column length more that 100 char width then i am getting the following error / warning. We are trying to insert data into that table using java program and it is not inserting for this table.
Error message:
Warning: The table "BULKTABLE308" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
Can you please let us know if any workaround or is MS SQL server limitation.
Thanks,
Venkat

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Your maximum rowsize with that set up is 35,000 characters. SQL Server really doesn't support that.

Why such a huge table? What possible use is 350 columns of 100 characters each?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

gvrsony SSC Veteran Points: 299 More actions · Answer 2

We have very big tables in SAP with 350 columns like that... so we want to create same kind of table in SQL and transfer data. I can say all columns are not haveing 100chr length.

I would like to know what is the max columns we can have.

Thanks,

Venkat

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can have 1024 columns in a table, but the row size is 8060 bytes.

p.s. SQL 2000 or SQL 2005?

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

GSquared SSC Guru Points: 260824 More actions · Answer 4

So long as your data doesn't exceed 8k in any given row, you should be fine, so long as the columns are variable width.

SQL 2005 can allow data outside the 8k limit. Are you on SQL 2000? (Gail already asked, but I thought I'd clarify why.) It tends to get slow if you do it, though.

SQL 2008, with Sparse Columns, can do this kind of thing better.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GSquared (9/29/2011)
SQL 2005 can allow data outside the 8k limit. Are you on SQL 2000? (Gail already asked, but I thought I'd clarify why.) It tends to get slow if you do it, though.

and I asked, because SQL 2005 doesn't give that warning (I tested), because of the row overflow feature.

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

GSquared SSC Guru Points: 260824 More actions · Answer 6

GilaMonster (9/29/2011)
GSquared (9/29/2011)
SQL 2005 can allow data outside the 8k limit. Are you on SQL 2000? (Gail already asked, but I thought I'd clarify why.) It tends to get slow if you do it, though.
and I asked, because SQL 2005 doesn't give that warning (I tested), because of the row overflow feature.

Exactly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon