SQL Server Performance Forum – Threads Archive

Large Table vs Small Tables

Hi All, I have one frequently used table with 40 columns and i need to alter schema and could have 160 columns. Should i go for 160 columns in a single table or should i go for multiple child tables ? What about the Page fragmentation ? Whereas i can get maximum performance for insert/update/delete/select ? Thx

Are you repeating a detail, like Client1, Client2, etc.? Do some reading on normalization.

Are we talking about OLTP or OLAP?
ÃŒf it is OLTP, go for multiple smaller tables. Put the most frequently used columns into a table and those that frequenly can be NULL into other tables. —
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

No, i am not reapting any details.
Actually more details values needs to be stored in database. We required to store some more details corresponds to each column.And the relationship is one-to-one. for example… right now i am storing col1 only but now i requied to store
col1Attr1,col1Attr2,col1Attr3,col1Attr4 ( 4 attrib for each columns and i have 40 cols ) Database is in 3rd NF and col1 is not the primary key. So it would be better to go for another table ?
OR
it doesn’t make much difference if i create 160 columns in single table. PS. Table is accessed frequently.

quote:
right now i am storing col1 only but now i requied to store
col1Attr1,col1Attr2,col1Attr3,col1Attr4 ( 4 attrib for each columns and i have 40 cols )

I don’t know how you call this, but I would call it a repeating group which is violating 1 NF. Looks like you need some separated attribute table. —
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

http://www.datamodel.org/NormalizationRules.html Madhivanan Failing to plan is Planning to fail

]]>