2

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.

Improve this question
10
  • 4
    Nooooooooooooooo!!!!!! Commented Jul 23, 2014 at 1:27
  • 1
    I wouldn't worry too much about huge numbers of rows. SQL Server has been built to handle a tremendous amount of rows. Also once you have the data as rows you can create nice queries to summarise and report on it much more easily than if it's stored in json or something inside a field. xprogramming.com/Practices/PracSimplest.html Commented Jul 23, 2014 at 1:28
  • You might find this handy too stackoverflow.com/questions/759244/… Commented Jul 23, 2014 at 1:32
  • 4
    Your issue 'I don't want to store a huge number of rows' is a non-issue. This is how a a normalised design stores this kind of data. You can certainly store it in the way you wish but this will result in a LOT more visits to stackoverflow asking questions to get around this bad design. Commented Jul 23, 2014 at 1:48
  • 2
    "Array" or "List" in SQL Server is a table ! Commented Jul 23, 2014 at 4:46

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

...except for the obstacle of returning an aggregated view of number of (Correct Answers)/(Number of Answers) for a subset of identifying course location in a timely fashion for say a School, District or State.
Clearly you argue correctly against denormalizing 99.97% of the time for reasons you give an example of -- However, there is still the performance problem, what do you do when you have done EVERYTHING ELSE and it still too slow? Not your opinion that it is too slow, but it is measurably too slow -- performance considerations are breaking your application and you HAVE TO make it faster. You bite the bullet and denormalize and pay the consequences.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.