CODEWITHSUNDEEP
sqlsql-serverdatabase-performance
user1447679
user1447679

Reputation: 3250

In SQL Server, is there a performance hit on 200+ columns in a table, or better way to manage it?

I have a large form, with over 200 questions. Is there an issue with storing all these in a single table? Is there a better way?

Would it be better to have another table that holds values, like QuestionID, QuestionAnswer?

It would certainly be easier to keep it in one table, so just looking for direction to prevent regret later on :)

Upvotes: 0

Views: 315

Answers (2)

Simcha Khabinsky
Simcha Khabinsky

Reputation: 2039

Based on the fact that Change Will always Happen and maintainability:

It's better to change rows than columns - because changing data in rows is easy - but modifying table structure could become heavy and difficult.

So I would go the QuestionID, QuestionAnswer route.

For display purposes, you can always use the PIVOT function.

PIVOT can take data that looks like:

|AnswererID|Question      |Answer         |
===========================================
|1         |HowOldAreYou  |150 years      |
|2         |HowOldAreYou  |50 years       |
|1         |WhereDoYouLive|Florida        |
|2         |WhereDoYouLive|NewYork        |

And Transform it to look like this:

|AnswererID|HowOldAreYou|WhereDoYouLive|
========================================
|1         |150 years   |Florida       |
|2         |50 years    |NewYork       |

You can read more about PIVOT here: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: 1

Hogan
Hogan

Reputation: 70523

The standard answer is "It depends on your use case and requirements..."

However, I can't think of a single use case where it makes sense to have one column per answer for 200 answers from a design perspective.

I can think of many cases where a short-sighted designer would think that was "easier" and "faster". It's not.

Upvotes: 6

Related Questions