CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32808

Is there a way to store array like data in a column in SQL Server?

I have a table that holds Test Questions. Each row of the table contains details of a question for a test for a particular user.

The user is presented with 3-5 possible answers and I would like to store details of the answers that have been checked in the row. I don't really want to add new rows for every answer as this would create a huge number of rows.

Is there a way that I can store something like an array of answers in a column in SQL Server? Presently I am storing the data as a JSON string but I remember that Oracle had some way to store array data and I am wondering if SQL Server has the same.

Upvotes: 2

Views: 3532

Answers (1)

Gary Walker
Gary Walker

Reputation: 9134

Generally denormallizing is not a good idea. It is rarely a good idea idea. However, it is sometimes necessary for performance reasons. So, if not too slow, don't even consider it.

If you make a secondary answers table in your case with the TestQuestionID (or whatever you call the answers for a single question) to be the clustered index, it won't be much of a performance difference at all compared to a denormalized table.

If I were denormalizing your descriibed table I would probably just create 5 columns in the table, You could also use an xml field, but all you are storing is 5 answers, so I would not use xml in this simple case.

Since you are asking this question, you are not really a seasoned professional (we all start as novices) and you should consult the local sql expert before you denormalize.

ADDED CAVEAT,

Since you accepted this answer, you really need to understand for certain that denormalizing is almost always the wrong thing to do. That is why everyone, including me, was trying to tell you. Don't do this without talking to your DBA -- if you don't have a local DBA (unfortunately all too common) take the collective advice, and don't denormalize. I can think of only 1 time n my career that I think denormalizing was the correct solution. And I have bitten by the bad design (forced on me) by innappropriate denormlazation on many occasions.

Upvotes: 2

Related Questions