Performance question concerning varchar(max)

[Bill E.]

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

[Hugo Kornelis]

Re: Performance question concerning varchar(max)

On Wed, 23 Apr 2008 07:17:30 -0700 (PDT), Bill E. wrote:

(snip)

>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.

Hi Bill,

I'd think so too.

The only way to be sure is to test it. Preferably on the same hardware
and with the same data that your production system willl use.

Frankly though, I see no reason to choose the extra column. I do wonder
however if the column for the answer really has to be varchar(MAX). Are
you actually expecting ever to get answers over 8,000 characters in
length? You are aware that an average Word document has about 2,000
character per (full) page, are you?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Bill E.]

Re: Performance question concerning varchar(max)

Hugo,

Thanks for your input.

Bill