CODEWITHSUNDEEP
sqlsql-server-2005
TheTechGuy
TheTechGuy

Reputation: 17354

What is considered a large sql table considering number of fields

I know the question does not make that good of a sense.

Want to get an idea what is a good design, how big I can grow my table column wise. Is 100 columns or 200 columns in a table OK?

Upvotes: 1

Views: 268

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Well, for SQL Server 2005, according to the Max Capacity Specifications, the maximum number of columns per base table are 1024. So that's a hard upper limit.

Upvotes: 3

HLGEM
HLGEM

Reputation: 96572

You should consider the row size. 100 sparse columns is differnt from 100 varchar (3000) columns. It is almost always better to make a related table (with an enforced 1-1 relationship) when you are starting to get past the record size that SQL Server can store on one page.

You also should consider how the data will be queried. Are many of those fields ones that will not frequently need to be returned? Do they have a natural grouping (think a person record vice a user login record) and will that natural grouping dictate how they will be queried? In that case it might be better to separate them out.

And of course you should consider normalization. If you are doing multiple columns to avoid having a one-to-many relationship and a join, then you should not do that even if you only have 6 columns. Joins (with the key fields indexed) are preferable to denormalized tables except in data warehousing situations in general. It is better if you don't have to add new columns because you need to store a new phone type.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40319

It totally depends upon the nature of the subject you are modeling. If you need one column, you need one; if you need 500, then you need 500. Properly designed, the size of the tables you end up with will always be "just right".

How big can they be, what performs well, what if you need more columns than you can physically stuff into SQL... those are all implementation, maintenance, and/or performance questions, and that's a different and secondary subject. Get the models right first, then worry about implementation.

Upvotes: 4

Icarus
Icarus

Reputation: 63966

You should normalize your tables to at least third normal form (3NF). That should be your primary guide.

Upvotes: 2

Related Questions