xml data type vs several records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » xml data type vs several records

xml data type vs several records

Rate Topic

Display Mode

Topic Options

Author

Message

Sarsoura

Posted Sunday, November 18, 2012 7:21 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 11:31 AM
Points: 74, Visits: 164

Hello
I am trying to save student answers to questions in a quiz I am looking for best performance and storage space. I do not want the student to get timed out while answering specially essay questions is it better to keep the student answer for each question in a quiz in a separate record or use xml data type to save all the answers to all the questions of the quiz in one field
i.e which of the following is a better design
CREATE TABLE [Quiz_Definition].[quiz_attempts_answers](
[Quizid] [int] NOT NULL,
[attemptid] [int] NOT NULL,
[studentid] [int] NOT NULL,
[layoutseq] [nvarchar](4000) NULL,
[Answers] [xml] NULL,
[timemodified] [datetime] NULL,
CONSTRAINT [PK_quiz_attempts_answers_1] PRIMARY KEY CLUSTERED
(
[Quizid] ASC,
[attemptid] ASC,
[studentid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

where
layoutseq is a list of questions in the order the student saw in this attempt
answers is questionid and studentanswer it includes all questions and answers the student saw in this attempt

vs
CREATE TABLE [Quiz_Definition].[Quiz_Essay_Attempt_Answers_201223](
[QuizID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[SectionID] [int] NOT NULL,
[AttemptID] [int] NOT NULL,
[QuestionID] [bigint] NOT NULL,
[AnswerText] [nvarchar](max) NOT NULL,
[TimeModified] [datetime] NOT NULL,
[TimeCreated] [datetime] NOT NULL,
[ModifiedBy] [int] NOT NULL,
[Score] [decimal](8, 2) NOT NULL,
CONSTRAINT [PK_Quiz_Essay_Attempt_Answers_201223] PRIMARY KEY CLUSTERED
(
[QuizID] ASC,
[StudentID] ASC,
[SectionID] ASC,
[AttemptID] ASC,
[QuestionID] ASC)
ie the student answer for each question is saved in a separate record

Post #1386043

Jeff Moden

Posted Sunday, November 18, 2012 11:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 32,893, Visits: 26,765

This is almost a duplicate of the question you posted at the following URL...
http://www.sqlservercentral.com/Forums/Topic1385878-392-1.aspx
... and it has been answered there, as well.

Having multiple posts on the same question only serves to divide the answers of the responders. For anyone that cares to respond, please respond to the post above so we can keep all of the answers together. Thanks.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1386082

Sarsoura

Posted Sunday, November 18, 2012 3:12 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 11:31 AM
Points: 74, Visits: 164

This is a different question.
Thanks

Post #1386104

Jeff Moden

Posted Monday, November 19, 2012 5:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 32,893, Visits: 26,765

Sarsoura (11/18/2012)

This is a different question.
Thanks

I disagree. It's worded differently but it boils down to the same thing. Which is the best way to store answers to multiple-choice questions that may have more than one correct answer along with essay-style questions. That, not withstanding, it is your post and if you want to say it's different, then we can do that. I'd still post the same answer as I did on the other thread. Ther answer that I posted will prevent timeouts and many other problems as well as giving you the fastest SELECTability for reporting and easiest/fastest/least resource intensive ability to check questions for correct answers.

--Jeff Moden

Post #1386325

« Prev Topic | Next Topic »

Permissions