why do you have more than 50 fields in your table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

why do you have more than 50 fields in your table?

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-01-09 : 06:34:20

Hi , i see a friend of us here in this forum doing this questions.

to be exact was jeff

I have some table with more than 50 column, and i can not see how to be smaller, since i need all the columns in the same table.

if i break in 2 tables
i will have to do a lot of joins, two inserts, two Updates etc

I dont see it as a improvment in a aplication, since BOL says
Maximun column per table is 1024.

then I ask is there any trouble with too many columns?

tks

C. Lages

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-09 : 07:04:28

Again it depends upon your application design. If your application is basically a reporting application which doesn't have to do much insert/updates, then you can have denormalized architecture which reduces no. of joins by keeping related data in single table (may be by scheduling a nightly job).

But if you have an OLTP application which has thousands of DML operations on the table, it's better to keep separate smaller tables for faster insert and avoid data updation/deletion anomaly.

Or you can have hybrid of the two where you keep separate tables for each. You can use transaction table for daily operations and create a denormalized table for reporting purpose (or you can use Analysis services).

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-09 : 08:36:49

[code]
SELECT [Columns] = ((MAX_colid+9) / 10) * 10, [Tables] = COUNT(*)
FROM
(
SELECT C.id, [MAX_colid] = MAX(C.colid)
FROM dbo.syscolumns AS C
JOIN dbo.sysobjects AS O
ON O.id = C.id
AND O.type = 'U' -- User Tables only
GROUP BY C.id
) AS X
GROUP BY ((MAX_colid+9) / 10) * 10
ORDER BY 1
[/code]
I have
[code]
Columns Tables
----------- -----------
10 113
20 141
30 36
40 20
50 15
60 10
70 7
80 5
90 2
[/code]
Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-09 : 09:01:20

You didn't post the table definition, so it’s hard to say.

Is the table in third normal form? If it isn't then, then it’s probably not a good design. If you don't know what third normal form is, then it's probably not a good design.

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-09 : 09:13:53

As MVJ says, please post a CREATE TABLE statement with your columns, and while we do not know your exact business requirements so we cannot be 100% sure, chances are we'll be able to at least take an intelligent guess as to whether or not it is normalized.

It is possible (but unlikely) that a 50-column table might be necessary or even a good design in some cases, but 9 times out of 10 it is because it has a structure like one of the following:

(these are lists of column names)

Account, Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

or

Account, Year, M1Amount, M1Units, M2Amount, M2Units, M3Amount, M3Units, .. etc ....

or

Project, Contact1, Contact2, Contact3, Manager1, Manager2, Manager3, .. etc ...

or

Company, Phone1, Phone2, Phone3, Fax, CellPhone, OfficePhone, BillingAddress, BillingCity, BillingState, BillingZIP, ShippingAddress, ShippingCity, ShippingState, ShippingZIP, .. etc ..

and so on ...

all of those are examples of poor design ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources