how many columnscan I have in sql 2008

Posted on 2011-10-03
Medium Priority
Last Modified: 2012-05-12
I have a really big table I would like to make in my sql db.  What is the maximum amount of columns I can have?  Any concerns?
Question by:al4629740
LVL 15

Expert Comment

by:Minh Võ Công
ID: 36907711

Author Comment

ID: 36907726
sooo, 30000?
LVL 19

Accepted Solution

Rimvis earned 2000 total points
ID: 36907985
Hi al4629740,

30000 is the limit for the "wide" table, where most of the columns contain NULLs. You should be more concerted about "Bytes per row" limit, which is 8,060 bytes. There are cases, when total amount of data in a row can exceed 8 KB, but that's another story and is not applicable to a table with insane number of columns :)

IMHO you should reconsider your database design. Maybe you should create "vertical" table?

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 25

Expert Comment

ID: 36908053
I totally agree with Rimvis. Its not the number of column but the size of the row that will decide the number of fields in the table.

Author Comment

ID: 36909356
How do I know how many bytes per row there will be?
LVL 19

Expert Comment

ID: 36909494

Look for row size calculations:

Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question