SQL Server 2005
Simple scenario - We want to store answers to survey questions. Some
questions require very short responses (one or two words) while others
require long essay type responses.
--Scenario 1 -- store all answers in one column, regardless of
question
CREATE TABLE Answers
(
AnswerID int identity PRIMARY KEY,
UserID int,
QuestionID int,
AnswerText varchar(max)
)
--Scenario 2 -- store answers to short questions in one column and
long ones in another
CREATE TABLE Answers
(
AnswerID int identity PRIMARY KEY,
UserID int,
QuestionID int,
ShortAnswerText varchar(50),
LongAnswerText varchar(max)
)
Assume an index on QuestionID
If we need to query the table as in Scenario 1 for short question 27
as in
SELECT UserID, AnswerText
FROM Answers
WHERE QuestionID = 27 And AnswerText Like '%headache%'
Will we suffer a performance penalty vs. querying Scenario 2 as in
SELECT UserID, ShortAnswerText
FROM Answers
WHERE QuestionID = 27 And ShortAnswerText Like '%headache%'
I would think that the optimizer would first use the index on
QuestionID and this would eliminate the "baggage" of having to sort
through the responses to long questions in the AnswerText column but
perhaps this isn't the case.
Bill E.
Hollywood, FL
