TSQL Optimal / Acceptable table column count

Question

What is the optimal / acceptable column count when you are designing a table that serves as a datastore for huge forms to be filled?

Answer 1

I don't think there is any optimal number of columns, as such.

Obviously you need to consider the maximum width of a row, which is just over 8000 bytes.

Consider the way you're going to be handling the data. Will most of the columns be used all the time, or are there groups of columns that are sometimes unused (e.g., on the form, questions 8-12 are only used when the answer to question 7 is "Yes"). In those cases, it might make sense to put the optional questions into a separate table.

Making the rows wider means that your data pages, and hence your clustered key (if any) will take up more space; hence you can fit fewer records onto a page, and that means you'll have to do more IO operations when data is retrieved from the table. Thus, if there are logical ways to partition the data so that the rows are narrower, you may improve your read access speeds.

You might even consider going one step farther in normalization, so that a single form entry has a header row in a parent table, and then each question in the form gets its own row in a child table.