120 columns Table vs 3x 40columns | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

120 columns Table vs 3x 40columns

Hi I’m trying to determine pros and cons of this situation: I have a form that delivers a data to a table with 120 columns – in worst case scenario the form would fill all the columns with data. Most of columns data types are approximately:
bit – 30%, smallint – 10%, varchar(50) – 20%, varchar(500) – 30%, In 100% of cases The Form fills only 40 columns,
In 35% of all cases it requires another 40 columns,
and finally in only 10% of all cases it requires last 40 columns. It would be great to divide that 120column table to 3 different tables in terms of database size advantage – by creating stored procedure that would pick up the parameters from the form and inserted them into the tables. But my concerns are connected to speed issues. Which of the solutions would be faster in terms of writes and reads? Are there any other issues I would have to think of? kris
– did you apply CODD’s normalforms to you dataanalysis to start with ?
– Determine your priotities : insert or Select/update/delete
– how many rows to insert per second/minute/hour/day ?
– how do you plan to organise (clustering) your data ?
– one flat row in stead of 3 organised rows. your io will differ as will the price and efforts you pay when retrieving and working with the data afterward.
Is this for a survey or questionaire?
Without having more detailed explanations, I would say that neither 1-120 nor 3-40 columns seems like a good solution. VARCHAR(50) and VARCHAR(500) seems like a one size-fits all data type. Do you really need them that wide? —
Frank Kalis
Microsoft SQL Server MVP